1 | <?php |
---|
2 | require_once(MOD_PATH.'Dictionaries/Projects/services/ProjectsService.inc'); |
---|
3 | require_once('./classes/FieldTools/RightCheckers/ControlPanelRightsChecker.inc'); |
---|
4 | require_once('./classes/Calendars/services/CalendarsDefService.inc'); |
---|
5 | require_once('./classes/UsefullDataSets/EmployeesDataSet.inc'); |
---|
6 | |
---|
7 | use PhpOffice\PhpSpreadsheet\Cell\Coordinate; |
---|
8 | use PhpOffice\PhpSpreadsheet\IOFactory; |
---|
9 | |
---|
10 | /** |
---|
11 | * ProjectsImportService |
---|
12 | * |
---|
13 | * @author Tomasz Świenty |
---|
14 | * @version 2.0 |
---|
15 | * @copyright Copyright (c) eDokumenty |
---|
16 | */ |
---|
17 | final class ProjectsImportService { |
---|
18 | |
---|
19 | |
---|
20 | const AVAILABLE_EXT = ['xls', 'xlsx', 'xlsm', 'xltx', 'xltm', 'xlt', 'xml', 'ods', 'ots']; |
---|
21 | |
---|
22 | |
---|
23 | const IF_FOUND_OPTION_BREAK = 'break'; |
---|
24 | const IF_FOUND_OPTION_SKIP = 'skip'; |
---|
25 | const IF_FOUND_OPTION_UPDATE = 'update'; |
---|
26 | |
---|
27 | |
---|
28 | const IF_FOUND_AND_DELETED_OPTION_BREAK = 'break'; |
---|
29 | const IF_FOUND_AND_DELETED_OPTION_SKIP = 'skip'; |
---|
30 | const IF_FOUND_AND_DELETED_OPTION_RESTORE = 'restore'; |
---|
31 | |
---|
32 | |
---|
33 | /** |
---|
34 | * @var string[] |
---|
35 | */ |
---|
36 | private static $colsMap = [ |
---|
37 | 'A' => 'projnm', |
---|
38 | 'B' => 'dscrpt', |
---|
39 | 'C' => 'number', |
---|
40 | 'D' => 'coorid', |
---|
41 | 'E' => 'contid', |
---|
42 | 'F' => 'start_', |
---|
43 | 'G' => 'end___', |
---|
44 | ]; |
---|
45 | |
---|
46 | |
---|
47 | /** |
---|
48 | * @var array |
---|
49 | */ |
---|
50 | private static $cache = []; |
---|
51 | |
---|
52 | |
---|
53 | /** |
---|
54 | * @param string $file |
---|
55 | * |
---|
56 | * @return bool |
---|
57 | * @throws Exception |
---|
58 | */ |
---|
59 | public static function checkFileExtension(string $file): bool { |
---|
60 | |
---|
61 | $info = pathinfo($file); |
---|
62 | if (!in_array($info['extension'], self::AVAILABLE_EXT)) { |
---|
63 | throw new Exception(sprintf(Translator::translate('Nieobsługiwane rozszerzenie pliku %s. Obsługiwane rozszerzenia to %s'), htmlspecialchars($info['extension'], ENT_QUOTES, 'UTF-8'), implode(', ', self::AVAILABLE_EXT))); |
---|
64 | } |
---|
65 | |
---|
66 | return TRUE; |
---|
67 | |
---|
68 | } |
---|
69 | |
---|
70 | |
---|
71 | /** |
---|
72 | * @param $file |
---|
73 | * @param array $options |
---|
74 | * @param ProgressBar|null $progressBar |
---|
75 | * |
---|
76 | * @return int |
---|
77 | * @throws UserRightsException |
---|
78 | * @throws \PhpOffice\PhpSpreadsheet\Exception |
---|
79 | * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception |
---|
80 | * @throws Exception |
---|
81 | */ |
---|
82 | public static function importFromFile($file, array $options = [], ?ProgressBar $progressBar = NULL): int { |
---|
83 | |
---|
84 | $rightsChecker = new ControlPanelRightsChecker(); |
---|
85 | if (!UserRights::checkSysAcc('bswfms.settings.level2') and !$rightsChecker->isPermitted('PROJECTS', FieldsAccessLevel::READ, TRUE)) { |
---|
86 | throw new UserRightsException(NULL, 'bswfms.settings.level2'); |
---|
87 | } |
---|
88 | |
---|
89 | $calendar = (new CalendarsDefService)->getDefaultCalendar(); |
---|
90 | if (!$calendar) { |
---|
91 | throw new Exception(Translator::translate('Brak domyślnego kalendarza')); |
---|
92 | } |
---|
93 | |
---|
94 | set_time_limit(0); |
---|
95 | ini_set('memory_limit', MAX_MEMORY_LIMIT); |
---|
96 | |
---|
97 | $options = [ |
---|
98 | 'ifFound' => ($options['ifFound'] ?? NULL), |
---|
99 | 'ifFoundAndDeleted' => ($options['ifFoundAndDeleted'] ?? NULL) |
---|
100 | ]; |
---|
101 | |
---|
102 | $IF_FOUND_OPTION = $options['ifFound'] ?? NULL; // break|skip|update |
---|
103 | $IF_FOUND_AND_DELETED_OPTION = $options['ifFoundAndDeleted'] ?? NULL; // skip|restore |
---|
104 | |
---|
105 | $reader = IOFactory::createReaderForFile($file); |
---|
106 | $reader->setReadDataOnly(FALSE); |
---|
107 | |
---|
108 | $spreadsheet = $reader->load($file); |
---|
109 | |
---|
110 | $worksheet = $spreadsheet->getActiveSheet(); |
---|
111 | |
---|
112 | $highestRow = $worksheet->getHighestRow(); |
---|
113 | $highestColumnIndex = Coordinate::columnIndexFromString(array_key_last(self::$colsMap)); |
---|
114 | |
---|
115 | $db = PgManager::getInstance(); |
---|
116 | |
---|
117 | // !!!!!!!!!!!!!!!!!!!!!!!! |
---|
118 | $db->commit(); |
---|
119 | $db->begin(); |
---|
120 | // !!!!!!!!!!!!!!!!!!!!!!!! |
---|
121 | |
---|
122 | $projectsService = new ProjectsService(); |
---|
123 | $projectsService->skipValidation = TRUE; |
---|
124 | |
---|
125 | $counter = 0; |
---|
126 | $progress = 0; |
---|
127 | |
---|
128 | ErrorHandler::tryBegin(); |
---|
129 | $columnsLetterIndexMap = []; |
---|
130 | |
---|
131 | try { |
---|
132 | if ($progressBar) { |
---|
133 | $progressBar->setMax(100); |
---|
134 | $progressBar->setProgress(0); |
---|
135 | } |
---|
136 | |
---|
137 | // dla każdego wiersza poczynając od drugiego (pierwszy to nazwy kolumny) |
---|
138 | for ($row = 2; $row <= $highestRow; ++$row) { |
---|
139 | $progress++; |
---|
140 | |
---|
141 | if ($progressBar) { |
---|
142 | $progressBar->setProgress((int)Number::mul(Number::div($progress, $highestRow, 2), 100, 2)); |
---|
143 | } |
---|
144 | |
---|
145 | $project = FALSE; |
---|
146 | $skipRow = FALSE; |
---|
147 | |
---|
148 | $dataMap = array_flip(self::$colsMap); |
---|
149 | |
---|
150 | $entry = []; |
---|
151 | |
---|
152 | // pobierz dane z komórek |
---|
153 | for ($columnIndex = 1; $columnIndex <= $highestColumnIndex; ++$columnIndex) { |
---|
154 | if (!isset($columnsLetterIndexMap[$columnIndex])) { |
---|
155 | $columnsLetterIndexMap[$columnIndex] = Coordinate::stringFromColumnIndex($columnIndex); |
---|
156 | } |
---|
157 | |
---|
158 | $entry[strtoupper($columnsLetterIndexMap[$columnIndex])] = $worksheet->getCellByColumnAndRow($columnIndex, $row)->getFormattedValue(); |
---|
159 | } |
---|
160 | |
---|
161 | $excelGridCoordinates = ':'.$row; |
---|
162 | |
---|
163 | try { |
---|
164 | $entry = array_filter($entry); |
---|
165 | if (!$entry) { |
---|
166 | continue; |
---|
167 | } |
---|
168 | |
---|
169 | array_walk($dataMap, function(&$value, &$key) use ($entry) { |
---|
170 | if (isset($entry[$value])) { |
---|
171 | $value = $entry[$value]; |
---|
172 | } else { |
---|
173 | $value = NULL; |
---|
174 | } |
---|
175 | }); |
---|
176 | |
---|
177 | try { |
---|
178 | foreach ($dataMap as $k => $v) { |
---|
179 | $excelGridCoordinates = array_search($k, self::$colsMap).':'.$row; |
---|
180 | |
---|
181 | $dataMap[$k] = self::mapData($k, $v); |
---|
182 | |
---|
183 | // custom validation for options |
---|
184 | if (in_array($k, ['projnm', 'number'])) { |
---|
185 | $out = PgManager::getInstance()->select('projects', 'projid, is_del', $k.' ~* E\'^'.Strings::sql_regex_prepare($dataMap[$k]).'$\' AND ent_id = '.LoggedUser::getEntityID(), FALSE, PGSQL_ASSOC); |
---|
186 | if (isset($out[0]['projid'])) { |
---|
187 | if (count($out) > 1) { |
---|
188 | throw new Exception(sprintf(Translator::translate('Znaleziono więcej niż jeden projekt dla warunku %s'), $k.' ~* E\'^'.Strings::sql_regex_prepare($dataMap[$k]).'$\'')); |
---|
189 | } |
---|
190 | |
---|
191 | // break|skip|update |
---|
192 | if ($IF_FOUND_OPTION === self::IF_FOUND_OPTION_BREAK) { |
---|
193 | throw new Exception(sprintf(Translator::translate('Znaleziono projekt dla warunku %s. Wybrano przerwanie importu.'), $k.' ~* E\'^'.Strings::sql_regex_prepare($dataMap[$k]).'$\'')); |
---|
194 | } |
---|
195 | |
---|
196 | if ($IF_FOUND_OPTION === self::IF_FOUND_OPTION_SKIP) { |
---|
197 | $skipRow = TRUE; |
---|
198 | } |
---|
199 | |
---|
200 | if ($IF_FOUND_OPTION === self::IF_FOUND_OPTION_UPDATE) { |
---|
201 | if ($out[0]['is_del'] === 't') { |
---|
202 | // break|skip|restore |
---|
203 | if ($IF_FOUND_AND_DELETED_OPTION === self::IF_FOUND_AND_DELETED_OPTION_BREAK) { |
---|
204 | throw new Exception(sprintf(Translator::translate('Znaleziono projekt dla warunku %s. Projekt jest usunięty. Wybrano przerwanie importu.'), $k.' ~* E\'^'.Strings::sql_regex_prepare($dataMap[$k]).'$\'')); |
---|
205 | } |
---|
206 | |
---|
207 | if ($IF_FOUND_AND_DELETED_OPTION === self::IF_FOUND_AND_DELETED_OPTION_SKIP) { |
---|
208 | $skipRow = TRUE; |
---|
209 | } |
---|
210 | |
---|
211 | if ($IF_FOUND_AND_DELETED_OPTION === self::IF_FOUND_AND_DELETED_OPTION_RESTORE) { |
---|
212 | PgManager::getInstance()->update('projects', ['is_del' => 'f'], 'projid = '.$out[0]['projid']); |
---|
213 | } |
---|
214 | } |
---|
215 | $project = $out[0]['projid']; |
---|
216 | } |
---|
217 | } |
---|
218 | } else { |
---|
219 | if (!$projectsService->isValid(FALSE, $k, $dataMap)) { |
---|
220 | throw new Exception(ProjectsService::getErrorMessage()); |
---|
221 | } |
---|
222 | } |
---|
223 | } |
---|
224 | } catch (Exception $exception) { |
---|
225 | $db->rollback(); |
---|
226 | throw $exception; |
---|
227 | } |
---|
228 | |
---|
229 | $dataMap['cadfid'] = $calendar->getPkeyValue(); |
---|
230 | } catch (Exception $exception) { |
---|
231 | throw new Exception(sprintf(Translator::translate('Wystąpił błąd w komórce %s: <br><br> %s'), '<b>'.$excelGridCoordinates.'</b>', $exception->getMessage()), 0, $exception); |
---|
232 | } |
---|
233 | |
---|
234 | if (!$skipRow) { |
---|
235 | if ($project) { |
---|
236 | $projectID = $projectsService->update($project, $dataMap); |
---|
237 | } else { |
---|
238 | $projectID = $projectsService->create($dataMap); |
---|
239 | } |
---|
240 | |
---|
241 | if (filter_var($projectID, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) === FALSE) { |
---|
242 | throw new Exception(ProjectsService::getErrorMessage()); |
---|
243 | } |
---|
244 | |
---|
245 | $counter++; |
---|
246 | } |
---|
247 | } |
---|
248 | } catch (Exception $exception) { |
---|
249 | $db->rollback(); |
---|
250 | throw $exception; |
---|
251 | } |
---|
252 | ErrorHandler::tryEnd(); |
---|
253 | |
---|
254 | if ($progressBar) { |
---|
255 | $progressBar->setProgress(100); |
---|
256 | } |
---|
257 | |
---|
258 | $db->commit(); |
---|
259 | |
---|
260 | return $counter; |
---|
261 | |
---|
262 | } |
---|
263 | |
---|
264 | |
---|
265 | /** |
---|
266 | * @param $dbColumnName |
---|
267 | * @param $value |
---|
268 | * @return mixed|string |
---|
269 | * @throws Exception |
---|
270 | */ |
---|
271 | private static function mapData($dbColumnName, $value) { |
---|
272 | |
---|
273 | $value = trim($value); |
---|
274 | |
---|
275 | switch ($dbColumnName) { |
---|
276 | case 'coorid': |
---|
277 | if (!$value) { |
---|
278 | throw new Exception(Translator::translate('Brak koordynatora projektu')); |
---|
279 | } |
---|
280 | |
---|
281 | if (isset(self::$cache['coorid'][$value])) { |
---|
282 | $value = self::$cache['coorid'][$value]; |
---|
283 | } else { |
---|
284 | $dataSet = new EmployeesDataSet(FALSE, TRUE); |
---|
285 | $dataSet->outputSQL = TRUE; |
---|
286 | |
---|
287 | if (filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) !== FALSE) { |
---|
288 | $out = PgManager::getInstance()->query('SELECT usr_id FROM ('.$dataSet->getAllData().') AS foo WHERE usr_id::TEXT = \''.pg_escape_string($value).'\'', FALSE, PGSQL_ASSOC); |
---|
289 | if (!isset($out[0]['usr_id'])) { |
---|
290 | throw new Exception(sprintf(Translator::translate('Koordynator o identyfikatorze %s nie został znaleziony w systemie'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
291 | } |
---|
292 | |
---|
293 | self::$cache['coorid'][$value] = $out[0]['usr_id']; |
---|
294 | $value = self::$cache['coorid'][$value]; |
---|
295 | } else { |
---|
296 | // szukam użytkownika systemu |
---|
297 | $sqlFilter = '(firnam||\' \'||lasnam ~* E\'^'.Strings::sql_regex_prepare($value).'$\' OR lasnam||\' \'||firnam ~* E\'^'.Strings::sql_regex_prepare($value).'$\')'; |
---|
298 | if (mb_strpos(trim($value), ' ') === FALSE) { |
---|
299 | $sqlFilter = 'usrnam ~* E\'^'.Strings::sql_regex_prepare($value).'$\''; |
---|
300 | } |
---|
301 | |
---|
302 | $out = PgManager::getInstance()->query('SELECT usr_id FROM ('.$dataSet->getWithColumns('usrnam').') AS foo WHERE '.$sqlFilter.'', FALSE, PGSQL_ASSOC); |
---|
303 | if (!isset($out[0]['usr_id'])) { |
---|
304 | throw new Exception(sprintf(Translator::translate('Koordynator o nazwie %s nie został znaleziony w systemie. <br> Wyczyść komórki zawierające błędną frazę lub wprowadź koordynatora (użytkownik systemu) do systemu.'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
305 | } |
---|
306 | |
---|
307 | if (count($out) > 1) { |
---|
308 | throw new Exception(sprintf(Translator::translate('Znaleziono więcej niż 1 dopasowań do frazy koordynatora %s'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
309 | } |
---|
310 | |
---|
311 | self::$cache['coorid'][$value] = $out[0]['usr_id']; |
---|
312 | $value = self::$cache['coorid'][$value]; |
---|
313 | } |
---|
314 | } |
---|
315 | |
---|
316 | break; |
---|
317 | |
---|
318 | case 'contid': |
---|
319 | if ($value) { |
---|
320 | if (isset(self::$cache['contid'][$value])) { |
---|
321 | $value = self::$cache['contid'][$value]; |
---|
322 | } else { |
---|
323 | if (filter_var($value, FILTER_VALIDATE_INT, ['options' => ['min_range' => 1]]) !== FALSE) { |
---|
324 | $out = PgManager::getInstance()->query('SELECT contid FROM contacts WHERE ent_id = '.LoggedUser::getEntityID().' AND is_del IS NOT TRUE AND contid::TEXT = \''.pg_escape_string($value).'\'', FALSE, PGSQL_ASSOC); |
---|
325 | if (!isset($out[0]['contid'])) { |
---|
326 | throw new Exception(sprintf(Translator::translate('Klient o identyfikatorze %s nie został znaleziony w systemie'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
327 | } |
---|
328 | |
---|
329 | if (count($out) > 1) { |
---|
330 | throw new Exception(sprintf(Translator::translate('Znaleziono więcej niż 1 dopasowań do frazy klienta %s'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
331 | } |
---|
332 | |
---|
333 | self::$cache['contid'][$value] = $out[0]['contid']; |
---|
334 | $value = self::$cache['contid'][$value]; |
---|
335 | } else { |
---|
336 | $out = PgManager::getInstance()->query('SELECT contid FROM contacts WHERE ent_id = '.LoggedUser::getEntityID().' AND is_del IS NOT TRUE AND (name_1::TEXT ~* E\'^'.Strings::sql_regex_prepare($value).'$\' OR name_2::TEXT ~* E\''.Strings::sql_regex_prepare($value).'\')', FALSE, PGSQL_ASSOC); |
---|
337 | if (!isset($out[0]['contid'])) { |
---|
338 | throw new Exception(sprintf(Translator::translate('Klient o nazwie %s nie został znaleziony w systemie. <br> Wyczyść komórki zawierające błędną frazę lub wprowadź klienta do systemu.'), htmlspecialchars($value, ENT_QUOTES, 'UTF-8'))); |
---|
339 | } |
---|
340 | |
---|
341 | self::$cache['contid'][$value] = $out[0]['contid']; |
---|
342 | $value = self::$cache['contid'][$value]; |
---|
343 | } |
---|
344 | } |
---|
345 | } |
---|
346 | break; |
---|
347 | } |
---|
348 | |
---|
349 | return $value; |
---|
350 | |
---|
351 | } |
---|
352 | |
---|
353 | } |
---|