dolibarr 20.0.0
import_csv.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 * Copyright (C) 2024 Frédéric France <frederic.france@free.fr>
7 *
8 * This program is free software; you can redistribute it and/or modify
9 * it under the terms of the GNU General Public License as published by
10 * the Free Software Foundation; either version 3 of the License, or
11 * (at your option) any later version.
12 *
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
17 *
18 * You should have received a copy of the GNU General Public License
19 * along with this program. If not, see <https://www.gnu.org/licenses/>.
20 * or see https://www.gnu.org/
21 */
22
29require_once DOL_DOCUMENT_ROOT.'/core/modules/import/modules_import.php';
30
31
36{
40 public $db;
41
45 public $id;
46
51 public $version = 'dolibarr';
52
53 public $label_lib; // Label of external lib used by driver
54
55 public $version_lib; // Version of external lib used by driver
56
57 public $separator;
58
59 public $file; // Path of file
60
61 public $handle; // Handle fichier
62
63 public $cacheconvert = array(); // Array to cache list of value found after a conversion
64
65 public $cachefieldtable = array(); // Array to cache list of value found into fields@tables
66
67 public $nbinsert = 0; // # of insert done during the import
68
69 public $nbupdate = 0; // # of update done during the import
70
71 public $charset = '';
72
73 public $col;
74
75
82 public function __construct($db, $datatoimport)
83 {
84 global $langs;
85
86 parent::__construct();
87 $this->db = $db;
88
89 $this->separator = (GETPOST('separator') ? GETPOST('separator') : getDolGlobalString('IMPORT_CSV_SEPARATOR_TO_USE', ','));
90 $this->enclosure = '"';
91 $this->escape = '"';
92
93 $this->id = 'csv'; // Same value then xxx in file name export_xxx.modules.php
94 $this->label = 'Csv'; // Label of driver
95 $this->desc = $langs->trans("CSVFormatDesc", $this->separator, $this->enclosure, $this->escape);
96 $this->extension = 'csv'; // Extension for generated file by this driver
97 $this->picto = 'mime/other'; // Picto
98 $this->version = '1.34'; // Driver version
99 $this->phpmin = array(7, 0); // Minimum version of PHP required by module
100
101 require_once DOL_DOCUMENT_ROOT.'/core/lib/admin.lib.php';
102 if (versioncompare($this->phpmin, versionphparray()) > 0) {
103 dol_syslog("Module need a higher PHP version");
104 $this->error = "Module need a higher PHP version";
105 return;
106 }
107
108 // If driver use an external library, put its name here
109 $this->label_lib = 'Dolibarr';
110 $this->version_lib = DOL_VERSION;
111
112 $this->datatoimport = $datatoimport;
113 if (preg_match('/^societe_/', $datatoimport)) {
114 $this->thirdpartyobject = new Societe($this->db);
115 }
116 }
117
118
119 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
126 public function write_header_example($outputlangs)
127 {
128 // phpcs:enable
129 return '';
130 }
131
132 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
140 public function write_title_example($outputlangs, $headerlinefields)
141 {
142 // phpcs:enable
143 $s = implode($this->separator, array_map('cleansep', $headerlinefields));
144 return $s."\n";
145 }
146
147 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
155 public function write_record_example($outputlangs, $contentlinevalues)
156 {
157 // phpcs:enable
158 $s = implode($this->separator, array_map('cleansep', $contentlinevalues));
159 return $s."\n";
160 }
161
162 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
169 public function write_footer_example($outputlangs)
170 {
171 // phpcs:enable
172 return '';
173 }
174
175
176 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
183 public function import_open_file($file)
184 {
185 // phpcs:enable
186 global $langs;
187 $ret = 1;
188
189 dol_syslog(get_class($this)."::open_file file=".$file);
190
191 ini_set('auto_detect_line_endings', 1); // For MAC compatibility
192
193 $this->handle = fopen(dol_osencode($file), "r");
194 if (!$this->handle) {
195 $langs->load("errors");
196 $this->error = $langs->trans("ErrorFailToOpenFile", $file);
197 $ret = -1;
198 } else {
199 $this->file = $file;
200 }
201
202 return $ret;
203 }
204
205
206 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
213 public function import_get_nb_of_lines($file)
214 {
215 // phpcs:enable
216 return dol_count_nb_of_line($file);
217 }
218
219
220 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
226 public function import_read_header()
227 {
228 // phpcs:enable
229 return 0;
230 }
231
232
233 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
239 public function import_read_record()
240 {
241 // phpcs:enable
242 global $conf;
243
244 $arrayres = fgetcsv($this->handle, 100000, $this->separator, $this->enclosure, $this->escape);
245
246 // End of file
247 if ($arrayres === false) {
248 return false;
249 }
250
251 //var_dump($this->handle);
252 //var_dump($arrayres);exit;
253 $newarrayres = array();
254 if ($arrayres && is_array($arrayres)) {
255 foreach ($arrayres as $key => $val) {
256 if (getDolGlobalString('IMPORT_CSV_FORCE_CHARSET')) { // Forced charset
257 if (strtolower($conf->global->IMPORT_CSV_FORCE_CHARSET) == 'utf8') {
258 $newarrayres[$key]['val'] = $val;
259 $newarrayres[$key]['type'] = (dol_strlen($val) ? 1 : -1); // If empty we consider it's null
260 } else {
261 $newarrayres[$key]['val'] = mb_convert_encoding($val, 'UTF-8', 'ISO-8859-1');
262 $newarrayres[$key]['type'] = (dol_strlen($val) ? 1 : -1); // If empty we consider it's null
263 }
264 } else { // Autodetect format (UTF8 or ISO)
265 if (utf8_check($val)) {
266 $newarrayres[$key]['val'] = $val;
267 $newarrayres[$key]['type'] = (dol_strlen($val) ? 1 : -1); // If empty we consider it's null
268 } else {
269 $newarrayres[$key]['val'] = mb_convert_encoding($val, 'UTF-8', 'ISO-8859-1');
270 $newarrayres[$key]['type'] = (dol_strlen($val) ? 1 : -1); // If empty we consider it's null
271 }
272 }
273 }
274
275 $this->col = count($newarrayres);
276 }
277
278 return $newarrayres;
279 }
280
281 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
287 public function import_close_file()
288 {
289 // phpcs:enable
290 fclose($this->handle);
291 return 0;
292 }
293
294
295 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps
307 public function import_insert($arrayrecord, $array_match_file_to_database, $objimport, $maxfields, $importid, $updatekeys)
308 {
309 // phpcs:enable
310 global $langs, $conf, $user;
311 global $thirdparty_static; // Specific to thirdparty import
312 global $tablewithentity_cache; // Cache to avoid to call desc at each rows on tables
313
314 $error = 0;
315 $warning = 0;
316 $this->errors = array();
317 $this->warnings = array();
318
319 //dol_syslog("import_csv.modules maxfields=".$maxfields." importid=".$importid);
320
321 //var_dump($array_match_file_to_database);
322 //var_dump($arrayrecord); exit;
323
324 $array_match_database_to_file = array_flip($array_match_file_to_database);
325 $sort_array_match_file_to_database = $array_match_file_to_database;
326 ksort($sort_array_match_file_to_database);
327
328 //var_dump($sort_array_match_file_to_database);
329
330 if (count($arrayrecord) == 0 || (count($arrayrecord) == 1 && empty($arrayrecord[0]['val']))) {
331 //print 'W';
332 $this->warnings[$warning]['lib'] = $langs->trans('EmptyLine');
333 $this->warnings[$warning]['type'] = 'EMPTY';
334 $warning++;
335 } else {
336 $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)
337 $updatedone = false;
338 $insertdone = false;
339 // For each table to insert, me make a separate insert
340 foreach ($objimport->array_import_tables[0] as $alias => $tablename) {
341 // Build sql request
342 $sql = '';
343 $listfields = array();
344 $listvalues = array();
345 $i = 0;
346 $errorforthistable = 0;
347
348 // Define $tablewithentity_cache[$tablename] if not already defined
349 if (!isset($tablewithentity_cache[$tablename])) { // keep this test with "isset"
350 dol_syslog("Check if table ".$tablename." has an entity field");
351 $resql = $this->db->DDLDescTable($tablename, 'entity');
352 if ($resql) {
353 $obj = $this->db->fetch_object($resql);
354 if ($obj) {
355 $tablewithentity_cache[$tablename] = 1; // table contains entity field
356 } else {
357 $tablewithentity_cache[$tablename] = 0; // table does not contain entity field
358 }
359 } else {
360 dol_print_error($this->db);
361 }
362 } else {
363 //dol_syslog("Table ".$tablename." check for entity into cache is ".$tablewithentity_cache[$tablename]);
364 }
365
366 // Define array to convert fields ('c.ref', ...) into column index (1, ...)
367 $arrayfield = array();
368 foreach ($sort_array_match_file_to_database as $key => $val) {
369 $arrayfield[$val] = ($key - 1);
370 }
371
372 // $arrayrecord start at key 0
373 // $sort_array_match_file_to_database start at key 1
374
375 // Loop on each fields in the match array: $key = 1..n, $val=alias of field (s.nom)
376 foreach ($sort_array_match_file_to_database as $key => $val) {
377 $fieldalias = preg_replace('/\..*$/i', '', $val);
378 $fieldname = preg_replace('/^.*\./i', '', $val);
379
380 if ($alias != $fieldalias) {
381 continue; // Not a field of current table
382 }
383
384 if ($key <= $maxfields) {
385 // Set $newval with value to insert and set $listvalues with sql request part for insert
386 $newval = '';
387 if ($arrayrecord[($key - 1)]['type'] > 0) {
388 $newval = $arrayrecord[($key - 1)]['val']; // If type of field into input file is not empty string (so defined into input file), we get value
389 }
390
391 //var_dump($newval);var_dump($val);
392 //var_dump($objimport->array_import_convertvalue[0][$val]);
393
394 // Make some tests on $newval
395
396 // Is it a required field ?
397 if (preg_match('/\*/', $objimport->array_import_fields[0][$val]) && ((string) $newval == '')) {
398 // @phan-suppress-next-line PhanPluginSuspiciousParamPosition
399 $this->errors[$error]['lib'] = $langs->trans('ErrorMissingMandatoryValue', $key);
400 $this->errors[$error]['type'] = 'NOTNULL';
401 $errorforthistable++;
402 $error++;
403 } else {
404 // Test format only if field is not a missing mandatory field (field may be a value or empty but not mandatory)
405 // We convert field if required
406 if (!empty($objimport->array_import_convertvalue[0][$val])) {
407 //print 'Must convert '.$newval.' with rule '.join(',',$objimport->array_import_convertvalue[0][$val]).'. ';
408 if ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeid'
409 || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromref'
410 || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeorlabel'
411 ) {
412 // 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.
413 $isidorref = 'id';
414 if (!is_numeric($newval) && $newval != '' && !preg_match('/^id:/i', $newval)) {
415 $isidorref = 'ref';
416 }
417
418 $newval = preg_replace('/^(id|ref):/i', '', $newval); // Remove id: or ref: that was used to force if field is id or ref
419 //print 'Newval is now "'.$newval.'" and is type '.$isidorref."<br>\n";
420
421 if ($isidorref == 'ref') { // If value into input import file is a ref, we apply the function defined into descriptor
422 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
423 $class = $objimport->array_import_convertvalue[0][$val]['class'];
424 $method = $objimport->array_import_convertvalue[0][$val]['method'];
425 if ($this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval] != '') {
426 $newval = $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval];
427 } else {
428 $resultload = dol_include_once($file);
429 if (empty($resultload)) {
430 dol_print_error(null, 'Error trying to call file='.$file.', class='.$class.', method='.$method);
431 break;
432 }
433 $classinstance = new $class($this->db);
434 if ($class == 'CGenericDic') {
435 $classinstance->element = $objimport->array_import_convertvalue[0][$val]['element'];
436 $classinstance->table_element = $objimport->array_import_convertvalue[0][$val]['table_element'];
437 }
438
439 // Try the fetch from code or ref
440 $param_array = array('', $newval);
441 if ($class == 'AccountingAccount') {
442 //var_dump($arrayrecord[0]['val']);
443 /*include_once DOL_DOCUMENT_ROOT.'/accountancy/class/accountancysystem.class.php';
444 $tmpchartofaccount = new AccountancySystem($this->db);
445 $tmpchartofaccount->fetch(getDolGlobalInt('CHARTOFACCOUNTS'));
446 //var_dump($tmpchartofaccount->ref.' - '.$arrayrecord[0]['val']);
447 if ((! (getDolGlobalInt('CHARTOFACCOUNTS') > 0)) || $tmpchartofaccount->ref != $arrayrecord[0]['val'])
448 {
449 $this->errors[$error]['lib']=$langs->trans('ErrorImportOfChartLimitedToCurrentChart', $tmpchartofaccount->ref);
450 $this->errors[$error]['type']='RESTRICTONCURRENCTCHART';
451 $errorforthistable++;
452 $error++;
453 }*/
454 $param_array = array('', $newval, 0, $arrayrecord[0]['val']); // Param to fetch parent from account, in chart.
455 }
456 if ($class == 'CActionComm') {
457 $param_array = array($newval); // CActionComm fetch method have same parameter for id and code
458 }
459 $result = call_user_func_array(array($classinstance, $method), $param_array);
460
461 // If duplicate record found
462 if (!($classinstance->id != '') && $result == -2) {
463 $this->errors[$error]['lib'] = $langs->trans('ErrorMultipleRecordFoundFromRef', $newval);
464 $this->errors[$error]['type'] = 'FOREIGNKEY';
465 $errorforthistable++;
466 $error++;
467 }
468
469 // If not found, try the fetch from label
470 if (!($classinstance->id != '') && $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeorlabel') {
471 $param_array = array('', '', $newval);
472 call_user_func_array(array($classinstance, $method), $param_array);
473 }
474 $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval] = $classinstance->id;
475
476 //print 'We have made a '.$class.'->'.$method.' to get id from code '.$newval.'. ';
477 if ($classinstance->id != '') { // id may be 0, it is a found value
478 $newval = $classinstance->id;
479 } elseif (! $error) {
480 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
481 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', num2Alpha($key - 1), $newval, 'code', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
482 } elseif (!empty($objimport->array_import_convertvalue[0][$val]['element'])) {
483 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldRefNotIn', num2Alpha($key - 1), $newval, $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['element']));
484 } else {
485 $this->errors[$error]['lib'] = 'ErrorBadDefinitionOfImportProfile';
486 }
487 $this->errors[$error]['type'] = 'FOREIGNKEY';
488 $errorforthistable++;
489 $error++;
490 }
491 }
492 }
493 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeandlabel') {
494 $isidorref = 'id';
495 if (!is_numeric($newval) && $newval != '' && !preg_match('/^id:/i', $newval)) {
496 $isidorref = 'ref';
497 }
498 $newval = preg_replace('/^(id|ref):/i', '', $newval);
499
500 if ($isidorref == 'ref') {
501 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
502 $class = $objimport->array_import_convertvalue[0][$val]['class'];
503 $method = $objimport->array_import_convertvalue[0][$val]['method'];
504 $codefromfield = $objimport->array_import_convertvalue[0][$val]['codefromfield'];
505 $code = $arrayrecord[$arrayfield[$codefromfield]]['val'];
506 if ($this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$code][$newval] != '') {
507 $newval = $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$code][$newval];
508 } else {
509 $resultload = dol_include_once($file);
510 if (empty($resultload)) {
511 dol_print_error(null, 'Error trying to call file='.$file.', class='.$class.', method='.$method.', code='.$code);
512 break;
513 }
514 $classinstance = new $class($this->db);
515 // Try the fetch from code and ref
516 $param_array = array('', $newval, $code);
517 call_user_func_array(array($classinstance, $method), $param_array);
518 $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$code][$newval] = $classinstance->id;
519 if ($classinstance->id > 0) { // we found record
520 $newval = $classinstance->id;
521 } else {
522 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
523 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', num2Alpha($key - 1), $newval, 'scale', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
524 } else {
525 $this->errors[$error]['lib'] = 'ErrorFieldValueNotIn';
526 }
527 $this->errors[$error]['type'] = 'FOREIGNKEY';
528 $errorforthistable++;
529 $error++;
530 }
531 }
532 }
533 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'zeroifnull') {
534 if (empty($newval)) {
535 $newval = '0';
536 }
537 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeunits' || $objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchscalefromcodeunits') {
538 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
539 $class = $objimport->array_import_convertvalue[0][$val]['class'];
540 $method = $objimport->array_import_convertvalue[0][$val]['method'];
541 $units = $objimport->array_import_convertvalue[0][$val]['units'];
542 if ($this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$units][$newval] != '') {
543 $newval = $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$units][$newval];
544 } else {
545 $resultload = dol_include_once($file);
546 if (empty($resultload)) {
547 dol_print_error(null, 'Error trying to call file='.$file.', class='.$class.', method='.$method.', units='.$units);
548 break;
549 }
550 $classinstance = new $class($this->db);
551 // Try the fetch from code or ref
552 call_user_func_array(array($classinstance, $method), array('', '', $newval, $units));
553 $scaleorid = (($objimport->array_import_convertvalue[0][$val]['rule'] == 'fetchidfromcodeunits') ? $classinstance->id : $classinstance->scale);
554 $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'.$units][$newval] = $scaleorid;
555 //print 'We have made a '.$class.'->'.$method." to get a value from key '".$newval."' and we got '".$scaleorid."'.";exit;
556 if ($classinstance->id > 0) { // we found record
557 $newval = $scaleorid ? $scaleorid : 0;
558 } else {
559 if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) {
560 $this->errors[$error]['lib'] = $langs->trans('ErrorFieldValueNotIn', num2Alpha($key - 1), $newval, 'scale', $langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
561 } else {
562 $this->errors[$error]['lib'] = 'ErrorFieldValueNotIn';
563 }
564 $this->errors[$error]['type'] = 'FOREIGNKEY';
565 $errorforthistable++;
566 $error++;
567 }
568 }
569 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getcustomercodeifauto') {
570 if (strtolower($newval) == 'auto') {
571 $this->thirdpartyobject->get_codeclient(0, 0);
572 $newval = $this->thirdpartyobject->code_client;
573 //print 'code_client='.$newval;
574 }
575 if (empty($newval)) {
576 $arrayrecord[($key - 1)]['type'] = -1; // If we get empty value, we will use "null"
577 }
578 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getsuppliercodeifauto') {
579 if (strtolower($newval) == 'auto') {
580 $this->thirdpartyobject->get_codefournisseur(0, 1);
581 $newval = $this->thirdpartyobject->code_fournisseur;
582 //print 'code_fournisseur='.$newval;
583 }
584 if (empty($newval)) {
585 $arrayrecord[($key - 1)]['type'] = -1; // If we get empty value, we will use "null"
586 }
587 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getcustomeraccountancycodeifauto') {
588 if (strtolower($newval) == 'auto') {
589 $this->thirdpartyobject->get_codecompta('customer');
590 $newval = $this->thirdpartyobject->code_compta;
591 //print 'code_compta='.$newval;
592 }
593 if (empty($newval)) {
594 $arrayrecord[($key - 1)]['type'] = -1; // If we get empty value, we will use "null"
595 }
596 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getsupplieraccountancycodeifauto') {
597 if (strtolower($newval) == 'auto') {
598 $this->thirdpartyobject->get_codecompta('supplier');
599 $newval = $this->thirdpartyobject->code_compta_fournisseur;
600 if (empty($newval)) {
601 $arrayrecord[($key - 1)]['type'] = -1; // If we get empty value, we will use "null"
602 }
603 //print 'code_compta_fournisseur='.$newval;
604 }
605 if (empty($newval)) {
606 $arrayrecord[($key - 1)]['type'] = -1; // If we get empty value, we will use "null"
607 }
608 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'getrefifauto') {
609 if (strtolower($newval) == 'auto') {
610 $defaultref = '';
611
612 $classModForNumber = $objimport->array_import_convertvalue[0][$val]['class'];
613 $pathModForNumber = $objimport->array_import_convertvalue[0][$val]['path'];
614
615 if (!empty($classModForNumber) && !empty($pathModForNumber) && is_readable(DOL_DOCUMENT_ROOT.$pathModForNumber)) {
616 require_once DOL_DOCUMENT_ROOT.$pathModForNumber;
617 $modForNumber = new $classModForNumber();
618
619 $tmpobject = null;
620 // Set the object with the date property when we can
621 if (!empty($objimport->array_import_convertvalue[0][$val]['classobject'])) {
622 $pathForObject = $objimport->array_import_convertvalue[0][$val]['pathobject'];
623 require_once DOL_DOCUMENT_ROOT.$pathForObject;
624 $tmpclassobject = $objimport->array_import_convertvalue[0][$val]['classobject'];
625 $tmpobject = new $tmpclassobject($this->db);
626 foreach ($arrayfield as $tmpkey => $tmpval) { // $arrayfield is array('c.ref'=>0, ...)
627 if (in_array($tmpkey, array('t.date', 'c.date_commande'))) {
628 $tmpobject->date = dol_stringtotime($arrayrecord[$arrayfield[$tmpkey]]['val'], 1);
629 }
630 }
631 }
632
633 $defaultref = $modForNumber->getNextValue(null, $tmpobject);
634 }
635 if (is_numeric($defaultref) && $defaultref <= 0) { // If error
636 $defaultref = '';
637 }
638 $newval = $defaultref;
639 }
640 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'compute') {
641 $file = (empty($objimport->array_import_convertvalue[0][$val]['classfile']) ? $objimport->array_import_convertvalue[0][$val]['file'] : $objimport->array_import_convertvalue[0][$val]['classfile']);
642 $class = $objimport->array_import_convertvalue[0][$val]['class'];
643 $method = $objimport->array_import_convertvalue[0][$val]['method'];
644 $resultload = dol_include_once($file);
645 if (empty($resultload)) {
646 dol_print_error(null, 'Error trying to call file='.$file.', class='.$class.', method='.$method);
647 break;
648 }
649 $classinstance = new $class($this->db);
650 $res = call_user_func_array(array($classinstance, $method), array(&$arrayrecord, $arrayfield, ($key - 1)));
651 if (empty($classinstance->error) && empty($classinstance->errors)) {
652 $newval = $res; // We get new value computed.
653 } else {
654 $this->errors[$error]['type'] = 'CLASSERROR';
655 $this->errors[$error]['lib'] = implode(
656 "\n",
657 array_merge([$classinstance->error], $classinstance->errors)
658 );
659 $errorforthistable++;
660 $error++;
661 }
662 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'numeric') {
663 $newval = price2num($newval);
664 } elseif ($objimport->array_import_convertvalue[0][$val]['rule'] == 'accountingaccount') {
665 if (!getDolGlobalString('ACCOUNTING_MANAGE_ZERO')) {
666 $newval = rtrim(trim($newval), "0");
667 } else {
668 $newval = trim($newval);
669 }
670 }
671
672 //print 'Val to use as insert is '.$newval.'<br>';
673 }
674
675 // Test regexp
676 if (!empty($objimport->array_import_regex[0][$val]) && ($newval != '')) {
677 // If test regex string is "field@table" or "field@table:..." (means must exists into table ...)
678 $reg = array();
679 if (preg_match('/^(.+)@([^:]+)(:.+)?$/', $objimport->array_import_regex[0][$val], $reg)) {
680 $field = $reg[1];
681 $table = $reg[2];
682 $filter = !empty($reg[3]) ? substr($reg[3], 1) : '';
683
684 $cachekey = $field.'@'.$table;
685 if (!empty($filter)) {
686 $cachekey .= ':'.$filter;
687 }
688
689 // Load content of field@table into cache array
690 if (!is_array($this->cachefieldtable[$cachekey])) { // If content of field@table not already loaded into cache
691 $sql = "SELECT ".$field." as aliasfield FROM ".$table;
692 if (!empty($filter)) {
693 $sql .= ' WHERE '.$filter;
694 }
695
696 $resql = $this->db->query($sql);
697 if ($resql) {
698 $num = $this->db->num_rows($resql);
699 $i = 0;
700 while ($i < $num) {
701 $obj = $this->db->fetch_object($resql);
702 if ($obj) {
703 $this->cachefieldtable[$cachekey][] = $obj->aliasfield;
704 }
705 $i++;
706 }
707 } else {
708 dol_print_error($this->db);
709 }
710 }
711
712 // Now we check cache is not empty (should not) and key is into cache
713 if (!is_array($this->cachefieldtable[$cachekey]) || !in_array($newval, $this->cachefieldtable[$cachekey])) {
714 $tableforerror = $table;
715 if (!empty($filter)) {
716 $tableforerror .= ':'.$filter;
717 }
718 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorFieldValueNotIn', num2Alpha($key - 1), $newval, $field, $tableforerror);
719 $this->errors[$error]['type'] = 'FOREIGNKEY';
720 $errorforthistable++;
721 $error++;
722 }
723 } elseif (!preg_match('/'.$objimport->array_import_regex[0][$val].'/i', $newval)) {
724 // If test is just a static regex
725 //if ($key == 19) print "xxx".$newval."zzz".$objimport->array_import_regex[0][$val]."<br>";
726 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorWrongValueForField', num2Alpha($key - 1), $newval, $objimport->array_import_regex[0][$val]);
727 $this->errors[$error]['type'] = 'REGEX';
728 $errorforthistable++;
729 $error++;
730 }
731 }
732
733 // Check HTML injection
734 $inj = testSqlAndScriptInject($newval, 0);
735 if ($inj) {
736 $this->errors[$error]['lib'] = $langs->transnoentitiesnoconv('ErrorHtmlInjectionForField', num2Alpha($key - 1), dol_trunc($newval, 100));
737 $this->errors[$error]['type'] = 'HTMLINJECTION';
738 $errorforthistable++;
739 $error++;
740 }
741
742 // Other tests
743 // ...
744 }
745
746 // Define $listfields and $listvalues to build the SQL request
747 if (isModEnabled("socialnetworks") && strpos($fieldname, "socialnetworks") !== false) {
748 if (!in_array("socialnetworks", $listfields)) {
749 $listfields[] = "socialnetworks";
750 $socialkey = array_search("socialnetworks", $listfields); // Return position of 'socialnetworks' key in array
751 $listvalues[$socialkey] = '';
752 }
753 //var_dump($newval); var_dump($arrayrecord[($key - 1)]['type']);
754 if (!empty($newval) && $arrayrecord[($key - 1)]['type'] > 0) {
755 $socialkey = array_search("socialnetworks", $listfields); // Return position of 'socialnetworks' key in array
756 //var_dump('sk='.$socialkey); // socialkey=19
757 $socialnetwork = explode("_", $fieldname)[1];
758 if (empty($listvalues[$socialkey]) || $listvalues[$socialkey] == "null") {
759 $json = new stdClass();
760 $json->$socialnetwork = $newval;
761 $listvalues[$socialkey] = json_encode($json);
762 } else {
763 $jsondata = $listvalues[$socialkey];
764 $json = json_decode($jsondata);
765 $json->$socialnetwork = $newval;
766 $listvalues[$socialkey] = json_encode($json);
767 }
768 }
769 } else {
770 $listfields[] = $fieldname;
771 // Note: arrayrecord (and 'type') is filled with ->import_read_record called by import.php page before calling import_insert
772 if (empty($newval) && $arrayrecord[($key - 1)]['type'] < 0) {
773 $listvalues[] = ($newval == '0' ? (int) $newval : "null");
774 } elseif (empty($newval) && $arrayrecord[($key - 1)]['type'] == 0) {
775 $listvalues[] = "''";
776 } else {
777 $listvalues[] = "'".$this->db->escape($newval)."'";
778 }
779 }
780 }
781 $i++;
782 }
783
784 // We add hidden fields (but only if there is at least one field to add into table)
785 // We process here all the fields that were declared into the array $this->import_fieldshidden_array of the descriptor file.
786 // Previously we processed the ->import_fields_array.
787 if (!empty($listfields) && is_array($objimport->array_import_fieldshidden[0])) {
788 // Loop on each hidden fields to add them into listfields/listvalues
789 foreach ($objimport->array_import_fieldshidden[0] as $tmpkey => $tmpval) {
790 if (!preg_match('/^' . preg_quote($alias, '/') . '\./', $tmpkey)) {
791 continue; // Not a field of current table
792 }
793 $keyfield = preg_replace('/^' . preg_quote($alias, '/') . '\./', '', $tmpkey);
794
795 if (in_array($keyfield, $listfields)) { // avoid duplicates in insert
796 continue;
797 } elseif ($tmpval == 'user->id') {
798 $listfields[] = $keyfield;
799 $listvalues[] = ((int) $user->id);
800 } elseif (preg_match('/^lastrowid-/', $tmpval)) {
801 $tmp = explode('-', $tmpval);
802 $lastinsertid = (isset($last_insert_id_array[$tmp[1]])) ? $last_insert_id_array[$tmp[1]] : 0;
803 $listfields[] = $keyfield;
804 $listvalues[] = (int) $lastinsertid;
805 //print $tmpkey."-".$tmpval."-".$listfields."-".$listvalues."<br>";exit;
806 } elseif (preg_match('/^const-/', $tmpval)) {
807 $tmp = explode('-', $tmpval, 2);
808 $listfields[] = $keyfield;
809 $listvalues[] = "'".$this->db->escape($tmp[1])."'";
810 } elseif (preg_match('/^rule-/', $tmpval)) {
811 $fieldname = $tmpkey;
812 if (!empty($objimport->array_import_convertvalue[0][$fieldname])) {
813 if ($objimport->array_import_convertvalue[0][$fieldname]['rule'] == 'compute') {
814 $file = (empty($objimport->array_import_convertvalue[0][$fieldname]['classfile']) ? $objimport->array_import_convertvalue[0][$fieldname]['file'] : $objimport->array_import_convertvalue[0][$fieldname]['classfile']);
815 $class = $objimport->array_import_convertvalue[0][$fieldname]['class'];
816 $method = $objimport->array_import_convertvalue[0][$fieldname]['method'];
817 $type = $objimport->array_import_convertvalue[0][$fieldname]['type'];
818 $resultload = dol_include_once($file);
819 if (empty($resultload)) {
820 dol_print_error(null, 'Error trying to call file=' . $file . ', class=' . $class . ', method=' . $method);
821 break;
822 }
823 $classinstance = new $class($this->db);
824 $res = call_user_func_array(array($classinstance, $method), array(&$arrayrecord, $arrayfield, ($key - 1)));
825 if (empty($classinstance->error) && empty($classinstance->errors)) {
826 $fieldArr = explode('.', $fieldname);
827 if (count($fieldArr) > 0) {
828 $fieldname = $fieldArr[1];
829 }
830
831 // Set $listfields and $listvalues
832 $listfields[] = $fieldname;
833 if ($type == 'int') {
834 $listvalues[] = (int) $res;
835 } elseif ($type == 'double') {
836 $listvalues[] = (float) $res;
837 } else {
838 $listvalues[] = "'".$this->db->escape($res)."'";
839 }
840 } else {
841 $this->errors[$error]['type'] = 'CLASSERROR';
842 $this->errors[$error]['lib'] = implode(
843 "\n",
844 array_merge([$classinstance->error], $classinstance->errors)
845 );
846 $errorforthistable++;
847 $error++;
848 }
849 }
850 }
851 } else {
852 $this->errors[$error]['lib'] = 'Bad value of profile setup '.$tmpval.' for array_import_fieldshidden';
853 $this->errors[$error]['type'] = 'Import profile setup';
854 $error++;
855 }
856 }
857 }
858 //print 'listfields='.$listfields.'<br>listvalues='.$listvalues.'<br>';
859
860 // If no error for this $alias/$tablename, we have a complete $listfields and $listvalues that are defined
861 // so we can try to make the insert or update now.
862 if (!$errorforthistable) {
863 //print "$alias/$tablename/$listfields/$listvalues<br>";
864 if (!empty($listfields)) {
865 $updatedone = false;
866 $insertdone = false;
867
868 $is_table_category_link = false;
869 $fname = 'rowid';
870 if (strpos($tablename, '_categorie_') !== false) {
871 $is_table_category_link = true;
872 $fname = '*';
873 }
874
875 if (!empty($updatekeys)) {
876 // We do SELECT to get the rowid, if we already have the rowid, it's to be used below for related tables (extrafields)
877
878 if (empty($lastinsertid)) { // No insert done yet for a parent table
879 $sqlSelect = "SELECT ".$fname." FROM ".$tablename;
880 $data = array_combine($listfields, $listvalues);
881 $where = array(); // filters to forge SQL request
882 $filters = array(); // filters to forge output error message
883 foreach ($updatekeys as $key) {
884 $col = $objimport->array_import_updatekeys[0][$key];
885 $key = preg_replace('/^.*\./i', '', $key);
886 if (isModEnabled("socialnetworks") && strpos($key, "socialnetworks") !== false) {
887 $tmp = explode("_", $key);
888 $key = $tmp[0];
889 $socialnetwork = $tmp[1];
890 $jsondata = $data[$key];
891 $json = json_decode($jsondata);
892 $stringtosearch = json_encode($socialnetwork).':'.json_encode($json->$socialnetwork);
893 //var_dump($stringtosearch);
894 //var_dump($this->db->escape($stringtosearch)); // This provide a value for sql string (but not for a like)
895 $where[] = $key." LIKE '%".$this->db->escape($this->db->escapeforlike($stringtosearch))."%'";
896 $filters[] = $col." LIKE '%".$this->db->escape($this->db->escapeforlike($stringtosearch))."%'";
897 //var_dump($where[1]); // This provide a value for sql string inside a like
898 } else {
899 $where[] = $key.' = '.$data[$key];
900 $filters[] = $col.' = '.$data[$key];
901 }
902 }
903 if (!empty($tablewithentity_cache[$tablename])) {
904 $where[] = "entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
905 $filters[] = "entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
906 }
907 $sqlSelect .= " WHERE ".implode(' AND ', $where);
908
909 $resql = $this->db->query($sqlSelect);
910 if ($resql) {
911 $num_rows = $this->db->num_rows($resql);
912 if ($num_rows == 1) {
913 $res = $this->db->fetch_object($resql);
914 $lastinsertid = $res->rowid;
915 if ($is_table_category_link) {
916 $lastinsertid = 'linktable';
917 } // 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
918 $last_insert_id_array[$tablename] = $lastinsertid;
919 } elseif ($num_rows > 1) {
920 $this->errors[$error]['lib'] = $langs->trans('MultipleRecordFoundWithTheseFilters', implode(', ', $filters));
921 $this->errors[$error]['type'] = 'SQL';
922 $error++;
923 } else {
924 // No record found with filters, insert will be tried below
925 }
926 } else {
927 //print 'E';
928 $this->errors[$error]['lib'] = $this->db->lasterror();
929 $this->errors[$error]['type'] = 'SQL';
930 $error++;
931 }
932 } else {
933 // We have a last INSERT ID (got by previous pass), so we check if we have a row referencing this foreign key.
934 // This is required when updating table with some extrafields. When inserting a record in parent table, we can make
935 // a direct insert into subtable extrafields, but when me wake an update, the insertid is defined and the child record
936 // may already exists. So we rescan the extrafield table to know if record exists or not for the rowid.
937 // Note: For extrafield tablename, we have in importfieldshidden_array an entry 'extra.fk_object'=>'lastrowid-tableparent' so $keyfield is 'fk_object'
938 $sqlSelect = "SELECT rowid FROM ".$tablename;
939
940 if (empty($keyfield)) {
941 $keyfield = 'rowid';
942 }
943 $sqlSelect .= " WHERE ".$keyfield." = ".((int) $lastinsertid);
944
945 if (!empty($tablewithentity_cache[$tablename])) {
946 $sqlSelect .= " AND entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
947 }
948
949 $resql = $this->db->query($sqlSelect);
950 if ($resql) {
951 $res = $this->db->fetch_object($resql);
952 if ($this->db->num_rows($resql) == 1) {
953 // We have a row referencing this last foreign key, continue with UPDATE.
954 } else {
955 // No record found referencing this last foreign key,
956 // force $lastinsertid to 0 so we INSERT below.
957 $lastinsertid = 0;
958 }
959 } else {
960 //print 'E';
961 $this->errors[$error]['lib'] = $this->db->lasterror();
962 $this->errors[$error]['type'] = 'SQL';
963 $error++;
964 }
965 }
966
967 if (!empty($lastinsertid)) {
968 // We db escape social network field because he isn't in field creation
969 if (in_array("socialnetworks", $listfields)) {
970 $socialkey = array_search("socialnetworks", $listfields);
971 $tmpsql = $listvalues[$socialkey];
972 $listvalues[$socialkey] = "'".$this->db->escape($tmpsql)."'";
973 }
974
975 // Build SQL UPDATE request
976 $sqlstart = "UPDATE ".$tablename;
977
978 $data = array_combine($listfields, $listvalues);
979 $set = array();
980 foreach ($data as $key => $val) {
981 $set[] = $key." = ".$val; // $val was escaped/sanitized previously
982 }
983 $sqlstart .= " SET ".implode(', ', $set).", import_key = '".$this->db->escape($importid)."'";
984
985 if (empty($keyfield)) {
986 $keyfield = 'rowid';
987 }
988 $sqlend = " WHERE ".$keyfield." = ".((int) $lastinsertid);
989
990 if ($is_table_category_link) {
991 '@phan-var-force string[] $where';
992 $sqlend = " WHERE " . implode(' AND ', $where);
993 }
994
995 if (!empty($tablewithentity_cache[$tablename])) {
996 $sqlend .= " AND entity IN (".getEntity($this->getElementFromTableWithPrefix($tablename)).")";
997 }
998
999 $sql = $sqlstart.$sqlend;
1000
1001 // Run update request
1002 $resql = $this->db->query($sql);
1003 if ($resql) {
1004 // No error, update has been done. $this->db->db->affected_rows can be 0 if data hasn't changed
1005 $updatedone = true;
1006 } else {
1007 //print 'E';
1008 $this->errors[$error]['lib'] = $this->db->lasterror();
1009 $this->errors[$error]['type'] = 'SQL';
1010 $error++;
1011 }
1012 }
1013 }
1014
1015 // Update not done, we do insert
1016 if (!$error && !$updatedone) {
1017 // We db escape social network field because he isn't in field creation
1018 if (in_array("socialnetworks", $listfields)) {
1019 $socialkey = array_search("socialnetworks", $listfields);
1020 $tmpsql = $listvalues[$socialkey];
1021 $listvalues[$socialkey] = "'".$this->db->escape($tmpsql)."'";
1022 }
1023
1024 // Build SQL INSERT request
1025 $sqlstart = "INSERT INTO ".$tablename."(".implode(", ", $listfields).", import_key";
1026 $sqlend = ") VALUES(".implode(', ', $listvalues).", '".$this->db->escape($importid)."'";
1027 if (!empty($tablewithentity_cache[$tablename])) {
1028 $sqlstart .= ", entity";
1029 $sqlend .= ", ".$conf->entity;
1030 }
1031 if (!empty($objimport->array_import_tables_creator[0][$alias])) {
1032 $sqlstart .= ", ".$objimport->array_import_tables_creator[0][$alias];
1033 $sqlend .= ", ".$user->id;
1034 }
1035 $sql = $sqlstart.$sqlend.")";
1036 //dol_syslog("import_csv.modules", LOG_DEBUG);
1037
1038 // Run insert request
1039 if ($sql) {
1040 $resql = $this->db->query($sql);
1041 if ($resql) {
1042 if (!$is_table_category_link) {
1043 $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).
1044 }
1045 $insertdone = true;
1046 } else {
1047 //print 'E';
1048 $this->errors[$error]['lib'] = $this->db->lasterror();
1049 $this->errors[$error]['type'] = 'SQL';
1050 $error++;
1051 }
1052 }
1053 }
1054 }
1055 /*else
1056 {
1057 dol_print_error(null,'ErrorFieldListEmptyFor '.$alias."/".$tablename);
1058 }*/
1059 }
1060
1061 if ($error) {
1062 break;
1063 }
1064 }
1065
1066 if ($updatedone) {
1067 $this->nbupdate++;
1068 }
1069 if ($insertdone) {
1070 $this->nbinsert++;
1071 }
1072 }
1073
1074 return 1;
1075 }
1076}
1077
1084function cleansep($value)
1085{
1086 return str_replace(array(',', ';'), '/', $value);
1087}
versionphparray()
Return version PHP.
versioncompare($versionarray1, $versionarray2)
Compare 2 versions (stored into 2 arrays).
Definition admin.lib.php:68
Class to import CSV files.
write_header_example($outputlangs)
Output header of an example file for this format.
import_get_nb_of_lines($file)
Return nb of records.
import_insert($arrayrecord, $array_match_file_to_database, $objimport, $maxfields, $importid, $updatekeys)
Insert a record into database.
__construct($db, $datatoimport)
Constructor.
import_read_record()
Return array of next record in input file.
write_record_example($outputlangs, $contentlinevalues)
Output record of an example file for this format.
write_footer_example($outputlangs)
Output footer of an example file for this format.
import_close_file()
Close file handle.
import_read_header()
Input header line from file.
write_title_example($outputlangs, $headerlinefields)
Output title line of an example file for this format.
import_open_file($file)
Open input file.
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:427
dol_count_nb_of_line($file)
Count number of lines in a file.
dol_osencode($str)
Return a string encoded into OS filesystem encoding.
price2num($amount, $rounding='', $option=0)
Function that return a number with universal decimal format (decimal separator is '.
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.
GETPOST($paramname, $check='alphanohtml', $method=0, $filter=null, $options=null, $noreplace=0)
Return value of a param into GET or POST supervariable.
dol_print_error($db=null, $error='', $errors=null)
Displays error message system with all the information to facilitate the diagnosis and the escalation...
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.
getDolGlobalString($key, $default='')
Return dolibarr global constant string value.
utf8_check($str)
Check if a string is in UTF8.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='', $logcontext=null)
Write log message into outputs.
cleansep($value)
Clean a string from separator.
testSqlAndScriptInject($val, $type)
Security: WAF layer for SQL Injection and XSS Injection (scripts) protection (Filters on GET,...
Definition main.inc.php:123