dolibarr 18.0.6
import_xlsx.modules.php
Go to the documentation of this file.
1<?php
2/* Copyright (C) 2006-2012 Laurent Destailleur <eldy@users.sourceforge.net>
3 * Copyright (C) 2009-2012 Regis Houssin <regis.houssin@inodbox.com>
4 * Copyright (C) 2012 Christophe Battarel <christophe.battarel@altairis.fr>
5 * Copyright (C) 2012-2016 Juanjo Menent <jmenent@2byte.es>
6 *
7 * This program is free software; you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation; either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * This program is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with this program. If not, see <https://www.gnu.org/licenses/>.
19 * or see https://www.gnu.org/
20 */
21
28use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
29use PhpOffice\PhpSpreadsheet\Spreadsheet;
30use PhpOffice\PhpSpreadsheet\Style\Alignment;
31
32require_once DOL_DOCUMENT_ROOT . '/core/modules/import/modules_import.php';
33
34
39{
43 public $db;
44
45 public $datatoimport;
46
50 public $error = '';
51
55 public $errors = array();
56
60 public $id;
61
65 public $label;
66
67 public $extension; // Extension of files imported by driver
68
73 public $version = 'dolibarr';
74
75 public $label_lib; // Label of external lib used by driver
76
77 public $version_lib; // Version of external lib used by driver
78
79 public $separator;
80
81 public $file; // Path of file
82
83 public $handle; // Handle fichier
84
85 public $cacheconvert = array(); // Array to cache list of value found after a convertion
86
87 public $cachefieldtable = array(); // Array to cache list of value found into fields@tables
88
89 public $nbinsert = 0; // # of insert done during the import
90
91 public $nbupdate = 0; // # of update done during the import
92
93 public $workbook; // temporary import file
94
95 public $record; // current record
96
97 public $headers;
98
99
106 public function __construct($db, $datatoimport)
107 {
108 global $conf, $langs;
109
110 parent::__construct();
111 $this->db = $db;
112
113 // this is used as an extension from the example file code, so we have to put xlsx here !!!
114 $this->id = 'xlsx'; // Same value as xxx in file name export_xxx.modules.php
115 $this->label = 'Excel 2007'; // Label of driver
116 $this->desc = $langs->trans("Excel2007FormatDesc");
117 $this->extension = 'xlsx'; // Extension for generated file by this driver
118 $this->picto = 'mime/xls'; // Picto (This is not used by the example file code as Mime type, too bad ...)
119 $this->version = '1.0'; // Driver version
120
121 // If driver use an external library, put its name here
122 require_once DOL_DOCUMENT_ROOT.'/includes/phpoffice/phpspreadsheet/src/autoloader.php';
123 require_once DOL_DOCUMENT_ROOT.'/includes/Psr/autoloader.php';
124 require_once PHPEXCELNEW_PATH.'Spreadsheet.php';
125 $this->workbook = new Spreadsheet();
126
127 // If driver use an external library, put its name here
128 if (!class_exists('ZipArchive')) { // For Excel2007
129 $langs->load("errors");
130 $this->error = $langs->trans('ErrorPHPNeedModule', 'zip');
131 return -1;
132 }
133 $this->label_lib = 'PhpSpreadSheet';
134 $this->version_lib = '1.8.0';
135
136 $this->datatoimport = $datatoimport;
137 if (preg_match('/^societe_/', $datatoimport)) {
138 $this->thirdpartyobject = new Societe($this->db);
139 }
140 }
141
142
143 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
150 public function write_header_example($outputlangs)
151 {
152 // phpcs:enable
153 global $user, $conf, $langs, $file;
154 // create a temporary object, the final output will be generated in footer
155 $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs) . ' - Dolibarr ' . DOL_VERSION);
156 $this->workbook->getProperties()->setTitle($outputlangs->trans("Import") . ' - ' . $file);
157 $this->workbook->getProperties()->setSubject($outputlangs->trans("Import") . ' - ' . $file);
158 $this->workbook->getProperties()->setDescription($outputlangs->trans("Import") . ' - ' . $file);
159
160 $this->workbook->setActiveSheetIndex(0);
161 $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
162 $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
163
164 return '';
165 }
166
167 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
175 public function write_title_example($outputlangs, $headerlinefields)
176 {
177 // phpcs:enable
178 global $conf;
179 $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
180 $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
181
182 $col = 1;
183 foreach ($headerlinefields as $field) {
184 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, 1, $outputlangs->transnoentities($field));
185 // set autowidth
186 //$this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($col + 1))->setAutoSize(true);
187 $col++;
188 }
189
190 return ''; // final output will be generated in footer
191 }
192
193 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
201 public function write_record_example($outputlangs, $contentlinevalues)
202 {
203 // phpcs:enable
204 $col = 1;
205 $row = 2;
206 foreach ($contentlinevalues as $cell) {
207 $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, $row, $cell);
208 $col++;
209 }
210
211 return ''; // final output will be generated in footer
212 }
213
214 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
221 public function write_footer_example($outputlangs)
222 {
223 // phpcs:enable
224 // return the file content as a string
225 $tempfile = tempnam(sys_get_temp_dir(), 'dol');
226 $objWriter = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->workbook);
227 $objWriter->save($tempfile);
228 $this->workbook->disconnectWorksheets();
229 unset($this->workbook);
230
231 $content = file_get_contents($tempfile);
232 unlink($tempfile);
233 return $content;
234 }
235
236
237
238 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
245 public function import_open_file($file)
246 {
247 // phpcs:enable
248 global $langs;
249 $ret = 1;
250
251 dol_syslog(get_class($this) . "::open_file file=" . $file);
252
253 $reader = new Xlsx();
254 $this->workbook = $reader->load($file);
255 $this->record = 1;
256 $this->file = $file;
257
258 return $ret;
259 }
260
261
262 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
269 public function import_get_nb_of_lines($file)
270 {
271 // phpcs:enable
272 $reader = new Xlsx();
273 $this->workbook = $reader->load($file);
274
275 $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
276
277 $this->workbook->disconnectWorksheets();
278 unset($this->workbook);
279
280 return $rowcount;
281 }
282
283
284 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
290 public function import_read_header()
291 {
292 // phpcs:enable
293 // This is not called by the import code !!!
294 $this->headers = array();
295 $xlsx = new Xlsx();
296 $info = $xlsx->listWorksheetinfo($this->file);
297 $countcolumns = $info[0]['totalColumns'];
298 for ($col = 1; $col <= $countcolumns; $col++) {
299 $this->headers[$col] = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, 1)->getValue();
300 }
301 return 0;
302 }
303
304
305 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
311 public function import_read_record()
312 {
313 // phpcs:enable
314 global $conf;
315
316 $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
317 if ($this->record > $rowcount) {
318 return false;
319 }
320 $array = array();
321 $xlsx = new Xlsx();
322 $info = $xlsx->listWorksheetinfo($this->file);
323 $countcolumns = $info[0]['totalColumns'];
324 for ($col = 1; $col <= $countcolumns; $col++) {
325 $val = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, $this->record)->getValue();
326 $array[$col]['val'] = $val;
327 $array[$col]['type'] = (dol_strlen($val) ? 1 : -1); // If empty we consider it null
328 }
329 $this->record++;
330 return $array;
331 }
332
333 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
339 public function import_close_file()
340 {
341 // phpcs:enable
342 $this->workbook->disconnectWorksheets();
343 unset($this->workbook);
344 return 0;
345 }
346
347
348 // What is this doing here ? it is common to all imports, is should be in the parent class
349 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
361 public function import_insert($arrayrecord, $array_match_file_to_database, $objimport, $maxfields, $importid, $updatekeys)
362 {
363 // phpcs:enable
364 global $langs, $conf, $user;
365 global $thirdparty_static; // Specific to thirdparty import
366 global $tablewithentity_cache; // Cache to avoid to call desc at each rows on tables
367
368 $error = 0;
369 $warning = 0;
370 $this->errors = array();
371 $this->warnings = array();
372
373 //dol_syslog("import_csv.modules maxfields=".$maxfields." importid=".$importid);
374
375 //var_dump($array_match_file_to_database);
376 //var_dump($arrayrecord); exit;
377
378 $array_match_database_to_file = array_flip($array_match_file_to_database);
379 $sort_array_match_file_to_database = $array_match_file_to_database;
380 ksort($sort_array_match_file_to_database);
381
382 //var_dump($sort_array_match_file_to_database);
383
384 if (count($arrayrecord) == 0 || (count($arrayrecord) == 1 && empty($arrayrecord[1]['val']))) {
385 //print 'W';
386 $this->warnings[$warning]['lib'] = $langs->trans('EmptyLine');
387 $this->warnings[$warning]['type'] = 'EMPTY';
388 $warning++;
389 } else {
390 $last_insert_id_array = array(); // store the last inserted auto_increment id for each table, so that dependent tables can be inserted with the appropriate id (eg: extrafields fk_object will be set with the last inserted object's id)
391 $updatedone = false;
392 $insertdone = false;
393 // For each table to insert, me make a separate insert
394 foreach ($objimport->array_import_tables[0] as $alias => $tablename) {
395 // Build sql request
396 $sql = '';
397 $listfields = array();
398 $listvalues = array();
399 $i = 0;
400 $errorforthistable = 0;
401
402 // Define $tablewithentity_cache[$tablename] if not already defined
403 if (!isset($tablewithentity_cache[$tablename])) { // keep this test with "isset"
404 dol_syslog("Check if table " . $tablename . " has an entity field");
405 $resql = $this->db->DDLDescTable($tablename, 'entity');
406 if ($resql) {
407 $obj = $this->db->fetch_object($resql);
408 if ($obj) {
409 $tablewithentity_cache[$tablename] = 1; // table contains entity field
410 } else {
411 $tablewithentity_cache[$tablename] = 0; // table does not contain entity field
412 }
413 } else {
414 dol_print_error($this->db);
415 }
416 } else {
417 //dol_syslog("Table ".$tablename." check for entity into cache is ".$tablewithentity_cache[$tablename]);
418 }
419
420 // Define array to convert fields ('c.ref', ...) into column index (1, ...)
421 $arrayfield = array();
422 foreach ($sort_array_match_file_to_database as $key => $val) {
423 $arrayfield[$val] = ($key);
424 }
425
426 // $arrayrecord start at key 1
427 // $sort_array_match_file_to_database start at key 1
428
429 // Loop on each fields in the match array: $key = 1..n, $val=alias of field (s.nom)
430 foreach ($sort_array_match_file_to_database as $key => $val) {
431 $fieldalias = preg_replace('/\..*$/i', '', $val);
432 $fieldname = preg_replace('/^.*\./i', '', $val);
433
434 if ($alias != $fieldalias) {
435 continue; // Not a field of current table
436 }
437
438 if ($key <= $maxfields) {
439 // Set $newval with value to insert and set $listvalues with sql request part for insert
440 $newval = '';
441 if ($arrayrecord[($key)]['type'] > 0) {
442 $newval = $arrayrecord[($key)]['val']; // If type of field into input file is not empty string (so defined into input file), we get value
443 }
444
445 //var_dump($newval);var_dump($val);
446 //var_dump($objimport->array_import_convertvalue[0][$val]);
447
448 // Make some tests on $newval
449
450 // Is it a required field ?
451 if (preg_match('/\*/', $objimport->array_import_fields[0][$val]) && ((string) $newval == '')) {
452 $this->errors[$error]['lib'] = $langs->trans('ErrorMissingMandatoryValue', num2Alpha($key - 1));
453 $this->errors[$error]['type'] = 'NOTNULL';
454 $errorforthistable++;
455 $error++;
456 } else {
457 // Test format only if field is not a missing mandatory field (field may be a value or empty but not mandatory)
458 // We convert field if required
459 if (!empty($objimport->array_import_convertvalue[0][$val])) {
460 //print 'Must convert '.$newval.' with rule '.join(',',$objimport->array_import_convertvalue[0][$val]).'. ';
461 if ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeid'
462 || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromref'
463 || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeorlabel'
464 ) {
465 // New val can be an id or ref. If it start with id: it is forced to id, if it start with ref: it is forced to ref. It not, we try to guess.
466 $isidorref = 'id';
467 if (!is_numeric($newval) && $newval != '' && !preg_match('/^id:/i', $newval)) {
468 $isidorref = 'ref';
469 }
470 $newval = preg_replace('/^(id|ref):/i', '', $newval); // Remove id: or ref: that was used to force if field is id or ref
471 //print 'Newval is now "'.$newval.'" and is type '.$isidorref."<br>\n";
472
473 if ($isidorref == 'ref') { // If value into input import file is a ref, we apply the function defined into descriptor
474 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
475 $class = $objimport->array_import_convertvalue[0][$val]['class'];
476 $method = $objimport->array_import_convertvalue[0][$val]['method'];
477 if ($this->cacheconvert[$file . '_' . $class . '_' . $method . '_'][$newval] != '') {
478 $newval = $this->cacheconvert[$file . '_' . $class . '_' . $method . '_'][$newval];
479 } else {
480 $resultload = dol_include_once($file);
481 if (empty($resultload)) {
482 dol_print_error('', 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method);
483 break;
484 }
485 $classinstance = new $class($this->db);
486 if ($class == 'CGenericDic') {
487 $classinstance->element = $objimport->array_import_convertvalue[0][$val]['element'];
488 $classinstance->table_element = $objimport->array_import_convertvalue[0][$val]['table_element'];
489 }
490
491 // Try the fetch from code or ref
492 $param_array = array('', $newval);
493 if ($class == 'AccountingAccount') {
494 //var_dump($arrayrecord[0]['val']);
495 /*include_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountancysystem.class.php';
496 $tmpchartofaccount = new AccountancySystem($this->db);
497 $tmpchartofaccount->fetch(getDolGlobalInt('CHARTOFACCOUNTS'));
498 //var_dump($tmpchartofaccount->ref.' - '.$arrayrecord[0]['val']);
499 if ((! (getDolGlobalInt('CHARTOFACCOUNTS') > 0)) || $tmpchartofaccount->ref != $arrayrecord[0]['val'])
500 {
501 $this->errors[$error]['lib']=$langs->trans('ErrorImportOfChartLimitedToCurrentChart', $tmpchartofaccount->ref);
502 $this->errors[$error]['type']='RESTRICTONCURRENCTCHART';
503 $errorforthistable++;
504 $error++;
505 }*/
506 $param_array = array('', $newval, 0, $arrayrecord[0]['val']); // Param to fetch parent from account, in chart.
507 }
508
509 $result = call_user_func_array(array($classinstance, $method), $param_array);
510
511 // If duplicate record found
512 if (!($classinstance->id != '') && $result == -2) {
513 $this->errors[$error]['lib'] = $langs->trans('ErrorMultipleRecordFoundFromRef', $newval);
514 $this->errors[$error]['type'] = 'FOREIGNKEY';
515 $errorforthistable++;
516 $error++;
517 }
518
519 // If not found, try the fetch from label
520 if (!($classinstance->id != '') && $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeorlabel') {
521 $param_array = array('', '', $newval);
522 call_user_func_array(array($classinstance, $method), $param_array);
523 }
524 $this->cacheconvert[$file . '_' . $class . '_' . $method . '_'][$newval] = $classinstance->id;
525
526 //print 'We have made a '.$class.'->'.$method.' to get id from code '.$newval.'. ';
527 if ($classinstance->id != '') { // id may be 0, it is a found value
528 $newval = $classinstance->id;
529 } elseif (! $error) {
530 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
531 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', $key, $newval, 'code', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
532 } elseif (!empty($objimport->array_import_convertvalue[0][$val]['element'])) {
533 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldRefNotIn', $key, $newval, $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['element']));
534 } else {
535 $this->errors[$error]['lib'] = 'ErrorBadDefinitionOfImportProfile';
536 }
537 $this->errors[$error]['type'] = 'FOREIGNKEY';
538 $errorforthistable++;
539 $error++;
540 }
541 }
542 }
543 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeandlabel') {
544 $isidorref = 'id';
545 if (!is_numeric($newval) && $newval != '' && !preg_match('/^id:/i', $newval)) {
546 $isidorref = 'ref';
547 }
548 $newval = preg_replace('/^(id|ref):/i', '', $newval);
549
550 if ($isidorref == 'ref') {
551 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
552 $class = $objimport->array_import_convertvalue[0][$val]['class'];
553 $method = $objimport->array_import_convertvalue[0][$val]['method'];
554 $codefromfield = $objimport->array_import_convertvalue[0][$val]['codefromfield'];
555 $code = $arrayrecord[$arrayfield[$codefromfield]]['val'];
556 if ($this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $code][$newval] != '') {
557 $newval = $this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $code][$newval];
558 } else {
559 $resultload = dol_include_once($file);
560 if (empty($resultload)) {
561 dol_print_error('', 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method . ', code=' . $code);
562 break;
563 }
564 $classinstance = new $class($this->db);
565 // Try the fetch from code and ref
566 $param_array = array('', $newval, $code);
567 call_user_func_array(array($classinstance, $method), $param_array);
568 $this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $code][$newval] = $classinstance->id;
569 if ($classinstance->id > 0) { // we found record
570 $newval = $classinstance->id;
571 } else {
572 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
573 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', $key, $newval, 'scale', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
574 } else {
575 $this->errors[$error]['lib'] = 'ErrorFieldValueNotIn';
576 }
577 $this->errors[$error]['type'] = 'FOREIGNKEY';
578 $errorforthistable++;
579 $error++;
580 }
581 }
582 }
583 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'zeroifnull') {
584 if (empty($newval)) {
585 $newval = '0';
586 }
587 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeunits' || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchscalefromcodeunits') {
588 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
589 $class = $objimport->array_import_convertvalue[0][$val]['class'];
590 $method = $objimport->array_import_convertvalue[0][$val]['method'];
591 $units = $objimport->array_import_convertvalue[0][$val]['units'];
592 if ($this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $units][$newval] != '') {
593 $newval = $this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $units][$newval];
594 } else {
595 $resultload = dol_include_once($file);
596 if (empty($resultload)) {
597 dol_print_error('', 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method . ', units=' . $units);
598 break;
599 }
600 $classinstance = new $class($this->db);
601 // Try the fetch from code or ref
602 call_user_func_array(array($classinstance, $method), array('', '', $newval, $units));
603 $scaleorid = (($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeunits') ? $classinstance->id : $classinstance->scale);
604 $this->cacheconvert[$file . '_' . $class . '_' . $method . '_' . $units][$newval] = $scaleorid;
605 //print 'We have made a '.$class.'->'.$method." to get a value from key '".$newval."' and we got '".$scaleorid."'.";exit;
606 if ($classinstance->id > 0) { // we found record
607 $newval = $scaleorid ? $scaleorid : 0;
608 } else {
609 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
610 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', $key, $newval, 'scale', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
611 } else {
612 $this->errors[$error]['lib'] = 'ErrorFieldValueNotIn';
613 }
614 $this->errors[$error]['type'] = 'FOREIGNKEY';
615 $errorforthistable++;
616 $error++;
617 }
618 }
619 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getcustomercodeifauto') {
620 if (strtolower($newval) == 'auto') {
621 $this->thirdpartyobject->get_codeclient(0, 0);
622 $newval = $this->thirdpartyobject->code_client;
623 //print 'code_client='.$newval;
624 }
625 if (empty($newval)) {
626 $arrayrecord[($key)]['type'] = -1; // If we get empty value, we will use "null"
627 }
628 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getsuppliercodeifauto') {
629 if (strtolower($newval) == 'auto') {
630 $this->thirdpartyobject->get_codefournisseur(0, 1);
631 $newval = $this->thirdpartyobject->code_fournisseur;
632 //print 'code_fournisseur='.$newval;
633 }
634 if (empty($newval)) {
635 $arrayrecord[($key)]['type'] = -1; // If we get empty value, we will use "null"
636 }
637 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getcustomeraccountancycodeifauto') {
638 if (strtolower($newval) == 'auto') {
639 $this->thirdpartyobject->get_codecompta('customer');
640 $newval = $this->thirdpartyobject->code_compta;
641 //print 'code_compta='.$newval;
642 }
643 if (empty($newval)) {
644 $arrayrecord[($key)]['type'] = -1; // If we get empty value, we will use "null"
645 }
646 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getsupplieraccountancycodeifauto') {
647 if (strtolower($newval) == 'auto') {
648 $this->thirdpartyobject->get_codecompta('supplier');
649 $newval = $this->thirdpartyobject->code_compta_fournisseur;
650 if (empty($newval)) {
651 $arrayrecord[($key)]['type'] = -1; // If we get empty value, we will use "null"
652 }
653 //print 'code_compta_fournisseur='.$newval;
654 }
655 if (empty($newval)) {
656 $arrayrecord[($key)]['type'] = -1; // If we get empty value, we will use "null"
657 }
658 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getrefifauto') {
659 if (strtolower($newval) == 'auto') {
660 $defaultref = '';
661
662 $classModForNumber = $objimport->array_import_convertvalue[0][$val]['class'];
663 $pathModForNumber = $objimport->array_import_convertvalue[0][$val]['path'];
664
665 if (!empty($classModForNumber) && !empty($pathModForNumber) && is_readable(DOL_DOCUMENT_ROOT.$pathModForNumber)) {
666 require_once DOL_DOCUMENT_ROOT.$pathModForNumber;
667 $modForNumber = new $classModForNumber;
668
669 $tmpobject = null;
670 // Set the object with the date property when we can
671 if (!empty($objimport->array_import_convertvalue[0][$val]['classobject'])) {
672 $pathForObject = $objimport->array_import_convertvalue[0][$val]['pathobject'];
673 require_once DOL_DOCUMENT_ROOT.$pathForObject;
674 $tmpclassobject = $objimport->array_import_convertvalue[0][$val]['classobject'];
675 $tmpobject = new $tmpclassobject($this->db);
676 foreach ($arrayfield as $tmpkey => $tmpval) { // $arrayfield is array('c.ref'=>1, ...)
677 if (in_array($tmpkey, array('t.date', 'c.date_commande'))) {
678 $tmpobject->date = dol_stringtotime($arrayrecord[$arrayfield[$tmpkey]]['val'], 1);
679 }
680 }
681 }
682
683 $defaultref = $modForNumber->getNextValue(null, $tmpobject);
684 }
685 if (is_numeric($defaultref) && $defaultref <= 0) { // If error
686 $defaultref = '';
687 }
688 $newval = $defaultref;
689 }
690 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'compute') {
691 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
692 $class = $objimport->array_import_convertvalue[0][$val]['class'];
693 $method = $objimport->array_import_convertvalue[0][$val]['method'];
694 $resultload = dol_include_once($file);
695 if (empty($resultload)) {
696 dol_print_error('', 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method);
697 break;
698 }
699 $classinstance = new $class($this->db);
700 $res = call_user_func_array(array($classinstance, $method), array(&$arrayrecord, $arrayfield, $key));
701 if (empty($classinstance->error) && empty($classinstance->errors)) {
702 $newval = $res; // We get new value computed.
703 } else {
704 $this->errors[$error]['type'] = 'CLASSERROR';
705 $this->errors[$error]['lib'] = implode(
706 "\n",
707 array_merge([$classinstance->error], $classinstance->errors)
708 );
709 $errorforthistable++;
710 $error++;
711 }
712 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'numeric') {
713 $newval = price2num($newval);
714 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'accountingaccount') {
715 if (empty($conf->global->ACCOUNTING_MANAGE_ZERO)) {
716 $newval = rtrim(trim($newval), "0");
717 } else {
718 $newval = trim($newval);
719 }
720 }
721
722 //print 'Val to use as insert is '.$newval.'<br>';
723 }
724
725 // Test regexp
726 if (!empty($objimport->array_import_regex[0][$val]) && ($newval != '')) {
727 // If test is "Must exist in a field@table or field@table:..."
728 $reg = array();
729 if (preg_match('/^(.+)@([^:]+)(:.+)?$/', $objimport->array_import_regex[0][$val], $reg)) {
730 $field = $reg[1];
731 $table = $reg[2];
732 $filter = !empty($reg[3]) ? substr($reg[3], 1) : '';
733
734 $cachekey = $field . '@' . $table;
735 if (!empty($filter)) {
736 $cachekey .= ':' . $filter;
737 }
738
739 // Load content of field@table into cache array
740 if (!is_array($this->cachefieldtable[$cachekey])) { // If content of field@table not already loaded into cache
741 $sql = "SELECT " . $field . " as aliasfield FROM " . $table;
742 if (!empty($filter)) {
743 $sql .= ' WHERE ' . $filter;
744 }
745
746 $resql = $this->db->query($sql);
747 if ($resql) {
748 $num = $this->db->num_rows($resql);
749 $i = 0;
750 while ($i < $num) {
751 $obj = $this->db->fetch_object($resql);
752 if ($obj) {
753 $this->cachefieldtable[$cachekey][] = $obj->aliasfield;
754 }
755 $i++;
756 }
757 } else {
758 dol_print_error($this->db);
759 }
760 }
761
762 // Now we check cache is not empty (should not) and key is into cache
763 if (!is_array($this->cachefieldtable[$cachekey]) || !in_array($newval, $this->cachefieldtable[$cachekey])) {
764 $tableforerror = $table;
765 if (!empty($filter)) {
766 $tableforerror .= ':' . $filter;
767 }
768 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorFieldValueNotIn', $key, $newval, $field, $tableforerror);
769 $this->errors[$error]['type'] = 'FOREIGNKEY';
770 $errorforthistable++;
771 $error++;
772 }
773 } elseif (!preg_match('/' . $objimport->array_import_regex[0][$val] . '/i', $newval)) {
774 // If test is just a static regex
775 //if ($key == 19) print "xxx".$newval."zzz".$objimport->array_import_regex[0][$val]."<br>";
776 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorWrongValueForField', $key, $newval, $objimport->array_import_regex[0][$val]);
777 $this->errors[$error]['type'] = 'REGEX';
778 $errorforthistable++;
779 $error++;
780 }
781 }
782
783 // Check HTML injection
784 $inj = testSqlAndScriptInject($newval, 0);
785 if ($inj) {
786 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorHtmlInjectionForField', $key, dol_trunc($newval, 100));
787 $this->errors[$error]['type'] = 'HTMLINJECTION';
788 $errorforthistable++;
789 $error++;
790 }
791
792 // Other tests
793 // ...
794 }
795
796 // Define $listfields and $listvalues to build SQL request
797 if (isModEnabled("socialnetworks") && strpos($fieldname, "socialnetworks") !== false) {
798 if (!in_array("socialnetworks", $listfields)) {
799 $listfields[] = "socialnetworks";
800 $socialkey = array_search("socialnetworks", $listfields); // Return position of 'socialnetworks' key in array. Example socialkey=19
801 $listvalues[$socialkey] = '';
802 }
803 if (!empty($newval) && $arrayrecord[($key)]['type'] > 0) {
804 $socialkey = array_search("socialnetworks", $listfields); // Return position of 'socialnetworks' key in array. Example socialkey=19
805 $socialnetwork = explode("_", $fieldname)[1];
806 if (empty($listvalues[$socialkey]) || $listvalues[$socialkey] == "null") {
807 $json = new stdClass();
808 $json->$socialnetwork = $newval;
809 $listvalues[$socialkey] = json_encode($json);
810 } else {
811 $jsondata = $listvalues[$socialkey];
812 $json = json_decode($jsondata);
813 $json->$socialnetwork = $newval;
814 $listvalues[$socialkey] = json_encode($json);
815 }
816 }
817 } else {
818 $listfields[] = $fieldname;
819
820 // Note: arrayrecord (and 'type') is filled with ->import_read_record called by import.php page before calling import_insert
821 if (empty($newval) && $arrayrecord[($key)]['type'] < 0) {
822 $listvalues[] = ($newval == '0' ? $newval : "null");
823 } elseif (empty($newval) && $arrayrecord[($key)]['type'] == 0) {
824 $listvalues[] = "''";
825 } else {
826 $listvalues[] = "'".$this->db->escape($newval)."'";
827 }
828 }
829 }
830 $i++;
831 }
832
833 // We add hidden fields (but only if there is at least one field to add into table)
834 // We process here all the fields that were declared into the array $this->import_fieldshidden_array of the descriptor file.
835 // Previously we processed the ->import_fields_array.
836 if (!empty($listfields) && is_array($objimport->array_import_fieldshidden[0])) {
837 // Loop on each hidden fields to add them into listfields/listvalues
838 foreach ($objimport->array_import_fieldshidden[0] as $key => $val) {
839 if (!preg_match('/^'.preg_quote($alias, '/').'\./', $key)) {
840 continue; // Not a field of current table
841 }
842 if ($val == 'user->id') {
843 $listfields[] = preg_replace('/^' . preg_quote($alias, '/') . '\./', '', $key);
844 $listvalues[] = ((int) $user->id);
845 } elseif (preg_match('/^lastrowid-/', $val)) {
846 $tmp = explode('-', $val);
847 $lastinsertid = (isset($last_insert_id_array[$tmp[1]])) ? $last_insert_id_array[$tmp[1]] : 0;
848 $keyfield = preg_replace('/^' . preg_quote($alias, '/') . '\./', '', $key);
849 $listfields[] = $keyfield;
850 $listvalues[] = $lastinsertid;
851 //print $key."-".$val."-".$listfields."-".$listvalues."<br>";exit;
852 } elseif (preg_match('/^const-/', $val)) {
853 $tmp = explode('-', $val, 2);
854 $listfields[] = preg_replace('/^' . preg_quote($alias, '/') . '\./', '', $key);
855 $listvalues[] = "'".$this->db->escape($tmp[1])."'";
856 } elseif (preg_match('/^rule-/', $val)) {
857 $fieldname = $key;
858 if (!empty($objimport->array_import_convertvalue[0][$fieldname])) {
859 if ($objimport->array_import_convertvalue[0][$fieldname]['rule'] == 'compute') {
860 $file = (empty($objimport->array_import_convertvalue[0][$fieldname]['classfile']) ? $objimport->array_import_convertvalue[0][$fieldname]['file'] : $objimport->array_import_convertvalue[0][$fieldname]['classfile']);
861 $class = $objimport->array_import_convertvalue[0][$fieldname]['class'];
862 $method = $objimport->array_import_convertvalue[0][$fieldname]['method'];
863 $resultload = dol_include_once($file);
864 if (empty($resultload)) {
865 dol_print_error('', 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method);
866 break;
867 }
868 $classinstance = new $class($this->db);
869 $res = call_user_func_array(array($classinstance, $method), array(&$arrayrecord, $arrayfield, $key));
870 $fieldArr = explode('.', $fieldname);
871 if (count($fieldArr) > 0) {
872 $fieldname = $fieldArr[1];
873 }
874 $listfields[] = $fieldname;
875 $listvalues[] = $res;
876 } else {
877 $this->errors[$error]['type'] = 'CLASSERROR';
878 $this->errors[$error]['lib'] = implode(
879 "\n",
880 array_merge([$classinstance->error], $classinstance->errors)
881 );
882 $errorforthistable++;
883 $error++;
884 }
885 }
886 } else {
887 $this->errors[$error]['lib'] = 'Bad value of profile setup ' . $val . ' for array_import_fieldshidden';
888 $this->errors[$error]['type'] = 'Import profile setup';
889 $error++;
890 }
891 }
892 }
893 //print 'listfields='.$listfields.'<br>listvalues='.$listvalues.'<br>';
894
895 // If no error for this $alias/$tablename, we have a complete $listfields and $listvalues that are defined
896 // so we can try to make the insert or update now.
897 if (!$errorforthistable) {
898 //print "$alias/$tablename/$listfields/$listvalues<br>";
899 if (!empty($listfields)) {
900 $updatedone = false;
901 $insertdone = false;
902
903 $is_table_category_link = false;
904 $fname = 'rowid';
905 if (strpos($tablename, '_categorie_') !== false) {
906 $is_table_category_link = true;
907 $fname='*';
908 }
909
910 if (!empty($updatekeys)) {
911 // We do SELECT to get the rowid, if we already have the rowid, it's to be used below for related tables (extrafields)
912
913 if (empty($lastinsertid)) { // No insert done yet for a parent table
914 $sqlSelect = "SELECT ".$fname." FROM " . $tablename;
915
916 $data = array_combine($listfields, $listvalues);
917
918 $where = array(); // filters to forge SQL request
919 $filters = array(); // filters to forge output error message
920 foreach ($updatekeys as $key) {
921 $col = $objimport->array_import_updatekeys[0][$key];
922 $key = preg_replace('/^.*\./i', '', $key);
923 if (isModEnabled("socialnetworks") && strpos($key, "socialnetworks") !== false) {
924 $tmp = explode("_", $key);
925 $key = $tmp[0];
926 $socialnetwork = $tmp[1];
927 $jsondata = $data[$key];
928 $json = json_decode($jsondata);
929 $stringtosearch = json_encode($socialnetwork).':'.json_encode($json->$socialnetwork);
930 //var_dump($stringtosearch);
931 //var_dump($this->db->escape($stringtosearch)); // This provide a value for sql string (but not for a like)
932 $where[] = $key." LIKE '%".$this->db->escape($this->db->escapeforlike($stringtosearch))."%'";
933 $filters[] = $col." LIKE '%".$this->db->escape($this->db->escapeforlike($stringtosearch))."%'";
934 //var_dump($where[1]); // This provide a value for sql string inside a like
935 } else {
936 $where[] = $key.' = '.$data[$key];
937 $filters[] = $col.' = '.$data[$key];
938 }
939 }
940 if (!empty($tablewithentity_cache[$tablename])) {
941 $where[] = "entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
942 $filters[] = "entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
943 }
944 $sqlSelect .= " WHERE " . implode(' AND ', $where);
945
946 $resql = $this->db->query($sqlSelect);
947 if ($resql) {
948 $num_rows = $this->db->num_rows($resql);
949 if ($num_rows == 1) {
950 $res = $this->db->fetch_object($resql);
951 $lastinsertid = $res->rowid;
952 if ($is_table_category_link) $lastinsertid = 'linktable'; // used to apply update on tables like llx_categorie_product and avoid being blocked for all file content if at least one entry already exists
953 $last_insert_id_array[$tablename] = $lastinsertid;
954 } elseif ($num_rows > 1) {
955 $this->errors[$error]['lib'] = $langs->trans('MultipleRecordFoundWithTheseFilters', implode(', ', $filters));
956 $this->errors[$error]['type'] = 'SQL';
957 $error++;
958 } else {
959 // No record found with filters, insert will be tried below
960 }
961 } else {
962 //print 'E';
963 $this->errors[$error]['lib'] = $this->db->lasterror();
964 $this->errors[$error]['type'] = 'SQL';
965 $error++;
966 }
967 } else {
968 // We have a last INSERT ID (got by previous pass), so we check if we have a row referencing this foreign key.
969 // This is required when updating table with some extrafields. When inserting a record in parent table, we can make
970 // a direct insert into subtable extrafields, but when me wake an update, the insertid is defined and the child record
971 // may already exists. So we rescan the extrafield table to know if record exists or not for the rowid.
972 // Note: For extrafield tablename, we have in importfieldshidden_array an enty 'extra.fk_object'=>'lastrowid-tableparent' so $keyfield is 'fk_object'
973 $sqlSelect = "SELECT rowid FROM " . $tablename;
974
975
976 if (empty($keyfield)) {
977 $keyfield = 'rowid';
978 }
979 $sqlSelect .= " WHERE ".$keyfield." = ".((int) $lastinsertid);
980
981 if (!empty($tablewithentity_cache[$tablename])) {
982 $sqlSelect .= " AND entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
983 }
984
985 $resql = $this->db->query($sqlSelect);
986 if ($resql) {
987 $res = $this->db->fetch_object($resql);
988 if ($this->db->num_rows($resql) == 1) {
989 // We have a row referencing this last foreign key, continue with UPDATE.
990 } else {
991 // No record found referencing this last foreign key,
992 // force $lastinsertid to 0 so we INSERT below.
993 $lastinsertid = 0;
994 }
995 } else {
996 //print 'E';
997 $this->errors[$error]['lib'] = $this->db->lasterror();
998 $this->errors[$error]['type'] = 'SQL';
999 $error++;
1000 }
1001 }
1002
1003 if (!empty($lastinsertid)) {
1004 // We db escape social network field because he isn't in field creation
1005 if (in_array("socialnetworks", $listfields)) {
1006 $socialkey = array_search("socialnetworks", $listfields);
1007 $tmpsql = $listvalues[$socialkey];
1008 $listvalues[$socialkey] = "'".$this->db->escape($tmpsql)."'";
1009 }
1010
1011 // Build SQL UPDATE request
1012 $sqlstart = "UPDATE " . $tablename;
1013
1014 $data = array_combine($listfields, $listvalues);
1015 $set = array();
1016 foreach ($data as $key => $val) {
1017 $set[] = $key." = ".$val;
1018 }
1019 $sqlstart .= " SET " . implode(', ', $set);
1020
1021 if (empty($keyfield)) {
1022 $keyfield = 'rowid';
1023 }
1024 $sqlend = " WHERE " . $keyfield . " = ".((int) $lastinsertid);
1025
1026 if ($is_table_category_link) {
1027 $sqlend = " WHERE " . implode(' AND ', $where);
1028 }
1029
1030 if (!empty($tablewithentity_cache[$tablename])) {
1031 $sqlend .= " AND entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
1032 }
1033
1034 $sql = $sqlstart . $sqlend;
1035
1036 // Run update request
1037 $resql = $this->db->query($sql);
1038 if ($resql) {
1039 // No error, update has been done. $this->db->db->affected_rows can be 0 if data hasn't changed
1040 $updatedone = true;
1041 } else {
1042 //print 'E';
1043 $this->errors[$error]['lib'] = $this->db->lasterror();
1044 $this->errors[$error]['type'] = 'SQL';
1045 $error++;
1046 }
1047 }
1048 }
1049
1050 // Update not done, we do insert
1051 if (!$error && !$updatedone) {
1052 // We db escape social network field because he isn't in field creation
1053 if (in_array("socialnetworks", $listfields)) {
1054 $socialkey = array_search("socialnetworks", $listfields);
1055 $tmpsql = $listvalues[$socialkey];
1056 $listvalues[$socialkey] = "'".$this->db->escape($tmpsql)."'";
1057 }
1058
1059 // Build SQL INSERT request
1060 $sqlstart = "INSERT INTO " . $tablename . "(" . implode(", ", $listfields) . ", import_key";
1061 $sqlend = ") VALUES(" . implode(', ', $listvalues) . ", '" . $this->db->escape($importid) . "'";
1062 if (!empty($tablewithentity_cache[$tablename])) {
1063 $sqlstart .= ", entity";
1064 $sqlend .= ", " . $conf->entity;
1065 }
1066 if (!empty($objimport->array_import_tables_creator[0][$alias])) {
1067 $sqlstart .= ", " . $objimport->array_import_tables_creator[0][$alias];
1068 $sqlend .= ", " . $user->id;
1069 }
1070 $sql = $sqlstart . $sqlend . ")";
1071 //dol_syslog("import_xlsx.modules", LOG_DEBUG);
1072
1073 // Run insert request
1074 if ($sql) {
1075 $resql = $this->db->query($sql);
1076 if ($resql) {
1077 if (!$is_table_category_link) {
1078 $last_insert_id_array[$tablename] = $this->db->last_insert_id($tablename); // store the last inserted auto_increment id for each table, so that child tables can be inserted with the appropriate id. This must be done just after the INSERT request, else we risk losing the id (because another sql query will be issued somewhere in Dolibarr).
1079 }
1080 $insertdone = true;
1081 } else {
1082 //print 'E';
1083 $this->errors[$error]['lib'] = $this->db->lasterror();
1084 $this->errors[$error]['type'] = 'SQL';
1085 $error++;
1086 }
1087 }
1088 }
1089 }
1090 /*else
1091 {
1092 dol_print_error('','ErrorFieldListEmptyFor '.$alias."/".$tablename);
1093 }*/
1094 }
1095
1096 if ($error) {
1097 break;
1098 }
1099 }
1100
1101 if ($updatedone) {
1102 $this->nbupdate++;
1103 }
1104 if ($insertdone) {
1105 $this->nbinsert++;
1106 }
1107 }
1108
1109 return 1;
1110 }
1111}
Class to import Excel files.
__construct($db, $datatoimport)
Constructor.
write_record_example($outputlangs, $contentlinevalues)
Output record of an example file for this format.
import_open_file($file)
Open input file.
import_insert($arrayrecord, $array_match_file_to_database, $objimport, $maxfields, $importid, $updatekeys)
Insert a record into database.
write_footer_example($outputlangs)
Output footer of an example file for this format.
import_read_header()
Input header line from file.
write_header_example($outputlangs)
Output header of an example file for this format.
import_read_record()
Return array of next record in input file.
write_title_example($outputlangs, $headerlinefields)
Output title line of an example file for this format.
import_get_nb_of_lines($file)
Return nb of records.
import_close_file()
Close file handle.
Parent class for import file readers.
getElementFromTableWithPrefix($tableNameWithPrefix)
Get element from table name with prefix.
Class to manage third parties objects (customers, suppliers, prospects...)
dol_stringtotime($string, $gm=1)
Convert a string date into a GM Timestamps date Warning: YYYY-MM-DDTHH:MM:SS+02:00 (RFC3339) is not s...
Definition date.lib.php:409
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
dol_print_error($db='', $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.
if(!function_exists( 'dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
num2Alpha($n)
Return a numeric value into an Excel like column number.
dol_trunc($string, $size=40, $trunc='right', $stringencoding='UTF-8', $nodot=0, $display=0)
Truncate a string to a particular length adding '…' if string larger than length.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
testSqlAndScriptInject($val, $type)
Security: WAF layer for SQL Injection and XSS Injection (scripts) protection (Filters on GET,...
Definition main.inc.php:89