Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
94.90% covered (success)
94.90%
93 / 98
25.00% covered (danger)
25.00%
1 / 4
 CRAP
0.00% covered (danger)
0.00%
0 / 1
SpreadsheetDatabaseMapper
94.90% covered (success)
94.90%
93 / 98
25.00% covered (danger)
25.00%
1 / 4
 27.10
0.00% covered (danger)
0.00%
0 / 1
 __construct
100.00% covered (success)
100.00%
2 / 2
100.00% covered (success)
100.00%
1 / 1
 1
 insert
96.77% covered (success)
96.77%
30 / 31
0.00% covered (danger)
0.00%
0 / 1
 8
 select
91.18% covered (success)
91.18%
31 / 34
0.00% covered (danger)
0.00%
0 / 1
 10.07
 update
96.77% covered (success)
96.77%
30 / 31
0.00% covered (danger)
0.00%
0 / 1
 8
1<?php
2/**
3 * Jingga
4 *
5 * PHP Version 8.1
6 *
7 * @package   phpOMS\Utils\IO\Spreadsheet
8 * @copyright Dennis Eichhorn
9 * @license   OMS License 2.0
10 * @version   1.0.0
11 * @link      https://jingga.app
12 */
13declare(strict_types=1);
14
15namespace phpOMS\Utils\IO\Spreadsheet;
16
17use phpOMS\DataStorage\Database\Connection\ConnectionAbstract;
18use phpOMS\DataStorage\Database\Query\Builder;
19use phpOMS\Utils\IO\IODatabaseMapper;
20use phpOMS\Utils\StringUtils;
21
22/**
23 * Spreadsheet database mapper.
24 *
25 * @package phpOMS\Utils\IO\Spreadsheet
26 * @license OMS License 2.0
27 * @link    https://jingga.app
28 * @since   1.0.0
29 */
30class SpreadsheetDatabaseMapper implements IODatabaseMapper
31{
32    /**
33     * Database connection
34     *
35     * @var ConnectionAbstract
36     * @since 1.0.0
37     */
38    private ConnectionAbstract $con;
39
40    /**
41     * Path to source or destination
42     *
43     * @var string
44     * @since 1.0.0
45     */
46    private string $path = '';
47
48    /**
49     * Constructor.
50     *
51     * @param ConnectionAbstract $con  Database connection
52     * @param string             $path File path
53     *
54     * @since 1.0.0
55     */
56    public function __construct(ConnectionAbstract $con, string $path)
57    {
58        $this->con  = $con;
59        $this->path = $path;
60    }
61
62    /**
63     * {@inheritdoc}
64     */
65    public function insert() : void
66    {
67        $reader = null;
68        if (StringUtils::endsWith($this->path, '.xlsx')) {
69            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
70        } elseif (StringUtils::endsWith($this->path, '.ods')) {
71            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
72        } else {
73            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
74        }
75
76        $reader->setReadDataOnly(true);
77        $sheet = $reader->load($this->path);
78
79        $tables = $sheet->getSheetCount();
80        for ($i = 0; $i < $tables; ++$i) {
81            $sheet->setActiveSheetIndex($i);
82
83            $workSheet = $sheet->getSheet($i);
84            $table     = $workSheet->getTitle();
85            $titles    = [];
86
87            // get column titles
88            $column = 1;
89            while (!empty($value = $workSheet->getCell(StringUtils::intToAlphabet($column) . 1)->getCalculatedValue())) {
90                $titles[] = $value;
91                ++$column;
92            }
93
94            $columns = \count($titles);
95            if ($columns === 0) {
96                continue;
97            }
98
99            // insert data
100            $query = new Builder($this->con);
101            $query->insert(...$titles)->into($table);
102
103            $line = 2;
104            while (!empty($workSheet->getCell('A' . $line)->getCalculatedValue())) {
105                $cells = [];
106                for ($j = 1; $j <= $columns; ++$j) {
107                    $cells[] = $workSheet->getCell(StringUtils::intToAlphabet($j) . $line)->getCalculatedValue();
108                }
109
110                ++$line;
111
112                $query->values(...$cells);
113            }
114
115            $query->execute();
116        }
117    }
118
119    /**
120     * {@inheritdoc}
121     */
122    public function select(array $queries) : void
123    {
124        $sheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
125        $sheet->getProperties()
126            ->setCreator('Karaka')
127            ->setLastModifiedBy('Karaka')
128            ->setTitle('Database export')
129            ->setSubject('Database export')
130            ->setDescription('This document is automatically generated from a database export.');
131
132        $sheetCount = $sheet->getSheetCount();
133
134        foreach ($queries as $key => $query) {
135            $results = $query->execute()?->fetchAll(\PDO::FETCH_ASSOC);
136            if (!\is_array($results)) {
137                continue;
138            }
139
140            if ($key > $sheetCount - 1) {
141                $sheet->createSheet($key);
142            }
143
144            $workSheet = $sheet->setActiveSheetIndex($key);
145            $rows      = \count($results);
146
147            if ($rows < 1) {
148                break;
149            }
150
151            $colCount = \count($results[0]);
152            $columns  = \array_keys($results[0]);
153
154            // set column titles
155            for ($i = 1; $i <= $colCount; ++$i) {
156                $workSheet->setCellValue(StringUtils::intToAlphabet($i) . 1, $columns[$i - 1]);
157            }
158
159            // set data
160            $row = 2;
161            foreach ($results as $result) {
162                $col = 1;
163                foreach ($result as $value) {
164                    $workSheet->setCellValue(StringUtils::intToAlphabet($col) . $row, $value);
165                    ++$col;
166                }
167
168                ++$row;
169            }
170        }
171
172        if (StringUtils::endsWith($this->path, '.xlsx')) {
173            (new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($sheet))->save($this->path);
174        } elseif (StringUtils::endsWith($this->path, '.ods')) {
175            (new \PhpOffice\PhpSpreadsheet\Writer\Ods($sheet))->save($this->path);
176        } else {
177            (new \PhpOffice\PhpSpreadsheet\Writer\Xls($sheet))->save($this->path);
178        }
179    }
180
181    /**
182     * {@inheritdoc}
183     */
184    public function update() : void
185    {
186        $reader = null;
187        if (StringUtils::endsWith($this->path, '.xlsx')) {
188            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
189        } elseif (StringUtils::endsWith($this->path, '.ods')) {
190            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
191        } else {
192            $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
193        }
194
195        $reader->setReadDataOnly(true);
196        $sheet = $reader->load($this->path);
197
198        $tables = $sheet->getSheetCount();
199        for ($i = 0; $i < $tables; ++$i) {
200            $sheet->setActiveSheetIndex($i);
201
202            $workSheet = $sheet->getSheet($i);
203            $table     = $workSheet->getTitle();
204            $titles    = [];
205
206            // get column titles
207            $column = 1;
208            while (!empty($value = $workSheet->getCell(StringUtils::intToAlphabet($column) . 1)->getCalculatedValue())) {
209                $titles[] = $value;
210                ++$column;
211            }
212
213            $columns = \count($titles);
214            if ($columns === 0) {
215                continue;
216            }
217
218            $idCol = (string) \array_shift($titles);
219
220            // update data
221            $line = 2;
222            while (!empty($workSheet->getCell('A' . $line)->getCalculatedValue())) {
223                $query = new Builder($this->con);
224                $query->update($table);
225
226                for ($j = 2; $j <= $columns; ++$j) {
227                    $query->sets((string) $titles[$j - 2], $workSheet->getCell(StringUtils::intToAlphabet($j) . $line)->getCalculatedValue());
228                }
229
230                $query->where($idCol, '=', $workSheet->getCell('A' . $line)->getCalculatedValue());
231                $query->execute();
232
233                ++$line;
234            }
235        }
236    }
237}