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