dolibarr  9.0.0
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 <http://www.gnu.org/licenses/>.
19  * or see http://www.gnu.org/
20  */
21 
28 require_once DOL_DOCUMENT_ROOT .'/core/modules/import/modules_import.php';
29 
30 
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 $workbook; // temporary import file
86 
87  public $record; // current record
88 
89  public $headers;
90 
91 
98  function __construct($db,$datatoimport)
99  {
100  global $conf,$langs;
101  $this->db = $db;
102 
103  // this is used as an extension from the example file code, so we have to put xlsx here !!!
104  $this->id='xlsx'; // Same value as xxx in file name export_xxx.modules.php
105  $this->label='Excel 2007'; // Label of driver
106  $this->desc=$langs->trans("Excel2007FormatDesc");
107  $this->extension='xlsx'; // Extension for generated file by this driver
108  $this->picto='mime/xls'; // Picto (This is not used by the example file code as Mime type, too bad ...)
109  $this->version='1.0'; // Driver version
110 
111  // If driver use an external library, put its name here
112  require_once PHPEXCEL_PATH.'PHPExcel.php';
113  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
114  if (! class_exists('ZipArchive')) // For Excel2007, PHPExcel need ZipArchive
115  {
116  $langs->load("errors");
117  $this->error=$langs->trans('ErrorPHPNeedModule','zip');
118  return -1;
119  }
120  $this->label_lib='PhpExcel';
121  $this->version_lib='1.8.0';
122 
123  $this->datatoimport=$datatoimport;
124  if (preg_match('/^societe_/',$datatoimport)) $this->thirpartyobject=new Societe($this->db);
125  }
126 
127 
128  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
135  function write_header_example($outputlangs)
136  {
137  // phpcs:enable
138  global $user,$conf,$langs;
139  // create a temporary object, the final output will be generated in footer
140  if (!empty($conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR)) {
141  $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
142  $cacheSettings = array (
143  'dir' => $conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR
144  );
145  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
146  }
147 
148  $this->workbook = new PHPExcel();
149  $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - Dolibarr '.DOL_VERSION);
150  $this->workbook->getProperties()->setTitle($outputlangs->trans("Import").' - '.$file);
151  $this->workbook->getProperties()->setSubject($outputlangs->trans("Import").' - '.$file);
152  $this->workbook->getProperties()->setDescription($outputlangs->trans("Import").' - '.$file);
153 
154  $this->workbook->setActiveSheetIndex(0);
155  $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
156  $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
157 
158  return '';
159  }
160 
161  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
169  function write_title_example($outputlangs,$headerlinefields)
170  {
171  // phpcs:enable
172  global $conf;
173  $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
174  $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
175 
176  $col = 0;
177  foreach($headerlinefields as $field) {
178  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, 1, $outputlangs->transnoentities($field));
179  // set autowidth
180  //$this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($col + 1))->setAutoSize(true);
181  $col++;
182  }
183 
184  return ''; // final output will be generated in footer
185  }
186 
187  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
195  function write_record_example($outputlangs,$contentlinevalues)
196  {
197  // phpcs:enable
198  $col = 0;
199  $row = 2;
200  foreach($contentlinevalues as $cell) {
201  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, $row, $cell);
202  $col++;
203  }
204 
205  return ''; // final output will be generated in footer
206  }
207 
208  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
215  function write_footer_example($outputlangs)
216  {
217  // phpcs:enable
218  // return the file content as a string
219  $tempfile = tempnam(sys_get_temp_dir(), 'dol');
220  $objWriter = new PHPExcel_Writer_Excel2007($this->workbook);
221  $objWriter->save($tempfile);
222  $this->workbook->disconnectWorksheets();
223  unset($this->workbook);
224 
225  $content = file_get_contents($tempfile);
226  unlink($tempfile);
227  return $content;
228  }
229 
230 
231 
232  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
239  function import_open_file($file)
240  {
241  // phpcs:enable
242  global $langs;
243  $ret=1;
244 
245  dol_syslog(get_class($this)."::open_file file=".$file);
246 
247  $reader = new PHPExcel_Reader_Excel2007();
248  $this->workbook = $reader->load($file);
249  $this->record = 1;
250  $this->file = $file;
251 
252  return $ret;
253  }
254 
255 
256  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
263  function import_get_nb_of_lines($file)
264  {
265  // phpcs:enable
266  $reader = new PHPExcel_Reader_Excel2007();
267  $this->workbook = $reader->load($file);
268 
269  $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
270 
271  $this->workbook->disconnectWorksheets();
272  unset($this->workbook);
273 
274  return $rowcount;
275  }
276 
277 
278  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
285  {
286  // phpcs:enable
287  // This is not called by the import code !!!
288  $this->headers = array();
289  $colcount = PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
290  for($col=0;$col<$colcount;$col++) {
291  $this->headers[$col] = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, 1)->getValue();
292  }
293  return 0;
294  }
295 
296 
297  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
304  {
305  // phpcs:enable
306  global $conf;
307 
308  $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
309  if($this->record > $rowcount)
310  return false;
311  $array = array();
312  $colcount = PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn(0));
313  for($col=0;$col<$colcount;$col++) {
314  $val = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, $this->record)->getValue();
315  $array[$col]['val'] = $val;
316  $array[$col]['type'] = (dol_strlen($val)?1:-1); // If empty we consider it null
317  }
318  $this->record++;
319  return $array;
320  }
321 
322  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
328  function import_close_file()
329  {
330  // phpcs:enable
331  $this->workbook->disconnectWorksheets();
332  unset($this->workbook);
333  }
334 
335 
336  // What is this doing here ? it is common to all imports, is should be in the parent class
337  // phpcs:disable PEAR.NamingConventions.ValidFunctionName.NotCamelCaps
349  function import_insert($arrayrecord,$array_match_file_to_database,$objimport,$maxfields,$importid,$updatekeys)
350  {
351  // phpcs:enable
352  global $langs,$conf,$user;
353  global $thirdparty_static; // Specific to thirdparty import
354  global $tablewithentity_cache; // Cache to avoid to call desc at each rows on tables
355 
356  $error=0;
357  $warning=0;
358  $this->errors=array();
359  $this->warnings=array();
360 
361  //dol_syslog("import_csv.modules maxfields=".$maxfields." importid=".$importid);
362 
363  //var_dump($array_match_file_to_database);
364  //var_dump($arrayrecord);
365  $array_match_database_to_file=array_flip($array_match_file_to_database);
366  $sort_array_match_file_to_database=$array_match_file_to_database;
367  ksort($sort_array_match_file_to_database);
368 
369  //var_dump($sort_array_match_file_to_database);
370 
371  if (count($arrayrecord) == 0 || (count($arrayrecord) == 1 && empty($arrayrecord[0]['val'])))
372  {
373  //print 'W';
374  $this->warnings[$warning]['lib']=$langs->trans('EmptyLine');
375  $this->warnings[$warning]['type']='EMPTY';
376  $warning++;
377  }
378  else
379  {
380  $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)
381  $updatedone = false;
382  $insertdone = false;
383  // For each table to insert, me make a separate insert
384  foreach($objimport->array_import_tables[0] as $alias => $tablename)
385  {
386  // Build sql request
387  $sql='';
388  $listfields=array();
389  $listvalues=array();
390  $i=0;
391  $errorforthistable=0;
392 
393  // Define $tablewithentity_cache[$tablename] if not already defined
394  if (! isset($tablewithentity_cache[$tablename])) // keep this test with "isset"
395  {
396  dol_syslog("Check if table ".$tablename." has an entity field");
397  $resql=$this->db->DDLDescTable($tablename,'entity');
398  if ($resql)
399  {
400  $obj=$this->db->fetch_object($resql);
401  if ($obj) $tablewithentity_cache[$tablename]=1; // table contains entity field
402  else $tablewithentity_cache[$tablename]=0; // table does not contains entity field
403  }
404  else dol_print_error($this->db);
405  }
406  else
407  {
408  //dol_syslog("Table ".$tablename." check for entity into cache is ".$tablewithentity_cache[$tablename]);
409  }
410 
411 
412  // Loop on each fields in the match array: $key = 1..n, $val=alias of field (s.nom)
413  foreach($sort_array_match_file_to_database as $key => $val)
414  {
415  $fieldalias=preg_replace('/\..*$/i','',$val);
416  $fieldname=preg_replace('/^.*\./i','',$val);
417 
418  if ($alias != $fieldalias) continue; // Not a field of current table
419 
420  if ($key <= $maxfields)
421  {
422  // Set $newval with value to insert and set $listvalues with sql request part for insert
423  $newval='';
424  if ($arrayrecord[($key-1)]['type'] > 0) $newval=$arrayrecord[($key-1)]['val']; // If type of field into input file is not empty string (so defined into input file), we get value
425 
426  // Make some tests on $newval
427 
428  // Is it a required field ?
429  if (preg_match('/\*/',$objimport->array_import_fields[0][$val]) && ((string) $newval==''))
430  {
431  $this->errors[$error]['lib']=$langs->trans('ErrorMissingMandatoryValue',$key);
432  $this->errors[$error]['type']='NOTNULL';
433  $errorforthistable++;
434  $error++;
435  }
436  // Test format only if field is not a missing mandatory field (field may be a value or empty but not mandatory)
437  else
438  {
439  // We convert field if required
440  if (! empty($objimport->array_import_convertvalue[0][$val]))
441  {
442  //print 'Must convert '.$newval.' with rule '.join(',',$objimport->array_import_convertvalue[0][$val]).'. ';
443  if ($objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeid'
444  || $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromref'
445  || $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeorlabel'
446  )
447  {
448  // 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.
449  $isidorref='id';
450  if (! is_numeric($newval) && $newval != '' && ! preg_match('/^id:/i',$newval)) $isidorref='ref';
451  $newval=preg_replace('/^(id|ref):/i','',$newval); // Remove id: or ref: that was used to force if field is id or ref
452  //print 'Val is now '.$newval.' and is type '.$isidorref."<br>\n";
453 
454  if ($isidorref == 'ref') // If value into input import file is a ref, we apply the function defined into descriptor
455  {
456  $file=(empty($objimport->array_import_convertvalue[0][$val]['classfile'])?$objimport->array_import_convertvalue[0][$val]['file']:$objimport->array_import_convertvalue[0][$val]['classfile']);
457  $class=$objimport->array_import_convertvalue[0][$val]['class'];
458  $method=$objimport->array_import_convertvalue[0][$val]['method'];
459  if ($this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval] != '')
460  {
461  $newval=$this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval];
462  }
463  else
464  {
465  $resultload = dol_include_once($file);
466  if (empty($resultload))
467  {
468  dol_print_error('', 'Error trying to call file='.$file.', class='.$class.', method='.$method);
469  break;
470  }
471  $classinstance=new $class($this->db);
472  // Try the fetch from code or ref
473  call_user_func_array(array($classinstance, $method),array('', $newval));
474  // If not found, try the fetch from label
475  if (! ($classinstance->id != '') && $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeorlabel')
476  {
477  call_user_func_array(array($classinstance, $method),array('', '', $newval));
478  }
479  $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval]=$classinstance->id;
480  //print 'We have made a '.$class.'->'.$method.' to get id from code '.$newval.'. ';
481  if ($classinstance->id != '') // id may be 0, it is a found value
482  {
483  $newval=$classinstance->id;
484  }
485  else
486  {
487  if (!empty($objimport->array_import_convertvalue[0][$val]['dict'])) $this->errors[$error]['lib']=$langs->trans('ErrorFieldValueNotIn',$key,$newval,'code',$langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['dict']));
488  else if (!empty($objimport->array_import_convertvalue[0][$val]['element'])) $this->errors[$error]['lib']=$langs->trans('ErrorFieldRefNotIn',$key,$newval,$langs->transnoentitiesnoconv($objimport->array_import_convertvalue[0][$val]['element']));
489  else $this->errors[$error]['lib']='ErrorFieldValueNotIn';
490  $this->errors[$error]['type']='FOREIGNKEY';
491  $errorforthistable++;
492  $error++;
493  }
494  }
495  }
496  }
497  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='zeroifnull')
498  {
499  if (empty($newval)) $newval='0';
500  }
501  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getcustomercodeifauto')
502  {
503  if (strtolower($newval) == 'auto')
504  {
505  $this->thirpartyobject->get_codeclient(0,0);
506  $newval=$this->thirpartyobject->code_client;
507  //print 'code_client='.$newval;
508  }
509  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
510  }
511  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getsuppliercodeifauto')
512  {
513  if (strtolower($newval) == 'auto')
514  {
515  $newval=$this->thirpartyobject->get_codefournisseur(0,1);
516  $newval=$this->thirpartyobject->code_fournisseur;
517  //print 'code_fournisseur='.$newval;
518  }
519  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
520  }
521  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getcustomeraccountancycodeifauto')
522  {
523  if (strtolower($newval) == 'auto')
524  {
525  $this->thirpartyobject->get_codecompta('customer');
526  $newval=$this->thirpartyobject->code_compta;
527  //print 'code_compta='.$newval;
528  }
529  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
530  }
531  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getsupplieraccountancycodeifauto')
532  {
533  if (strtolower($newval) == 'auto')
534  {
535  $this->thirpartyobject->get_codecompta('supplier');
536  $newval=$this->thirpartyobject->code_compta_fournisseur;
537  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
538  //print 'code_compta_fournisseur='.$newval;
539  }
540  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
541  }
542  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getrefifauto')
543  {
544  $defaultref='';
545  // TODO provide the $modTask (module of generation of ref) as parameter of import_insert function
546  $obj = empty($conf->global->PROJECT_TASK_ADDON)?'mod_task_simple':$conf->global->PROJECT_TASK_ADDON;
547  if (! empty($conf->global->PROJECT_TASK_ADDON) && is_readable(DOL_DOCUMENT_ROOT ."/core/modules/project/task/".$conf->global->PROJECT_TASK_ADDON.".php"))
548  {
549  require_once DOL_DOCUMENT_ROOT ."/core/modules/project/task/".$conf->global->PROJECT_TASK_ADDON.'.php';
550  $modTask = new $obj;
551  $defaultref = $modTask->getNextValue(null,null);
552  }
553  if (is_numeric($defaultref) && $defaultref <= 0) $defaultref='';
554  $newval=$defaultref;
555  }
556 
557 
558  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='numeric')
559  {
560  $newval = price2num($newval);
561  }
562 
563  //print 'Val to use as insert is '.$newval.'<br>';
564  }
565 
566  // Test regexp
567  if (! empty($objimport->array_import_regex[0][$val]) && ($newval != ''))
568  {
569  // If test is "Must exist in a field@table"
570  if (preg_match('/^(.*)@(.*)$/',$objimport->array_import_regex[0][$val],$reg))
571  {
572  $field=$reg[1];
573  $table=$reg[2];
574 
575  // Load content of field@table into cache array
576  if (! is_array($this->cachefieldtable[$field.'@'.$table])) // If content of field@table not already loaded into cache
577  {
578  $sql="SELECT ".$field." as aliasfield FROM ".$table;
579  $resql=$this->db->query($sql);
580  if ($resql)
581  {
582  $num=$this->db->num_rows($resql);
583  $i=0;
584  while ($i < $num)
585  {
586  $obj=$this->db->fetch_object($resql);
587  if ($obj) $this->cachefieldtable[$field.'@'.$table][]=$obj->aliasfield;
588  $i++;
589  }
590  }
591  else
592  {
593  dol_print_error($this->db);
594  }
595  }
596 
597  // Now we check cache is not empty (should not) and key is into cache
598  if (! is_array($this->cachefieldtable[$field.'@'.$table]) || ! in_array($newval,$this->cachefieldtable[$field.'@'.$table]))
599  {
600  $this->errors[$error]['lib']=$langs->transnoentitiesnoconv('ErrorFieldValueNotIn',$key,$newval,$field,$table);
601  $this->errors[$error]['type']='FOREIGNKEY';
602  $errorforthistable++;
603  $error++;
604  }
605  }
606  // If test is just a static regex
607  else if (! preg_match('/'.$objimport->array_import_regex[0][$val].'/i',$newval))
608  {
609  //if ($key == 19) print "xxx".$newval."zzz".$objimport->array_import_regex[0][$val]."<br>";
610  $this->errors[$error]['lib']=$langs->transnoentitiesnoconv('ErrorWrongValueForField',$key,$newval,$objimport->array_import_regex[0][$val]);
611  $this->errors[$error]['type']='REGEX';
612  $errorforthistable++;
613  $error++;
614  }
615  }
616 
617  // Other tests
618  // ...
619  }
620 
621  // Define $listfields and $listvalues to build SQL request
622  $listfields[] = $fieldname;
623 
624  // Note: arrayrecord (and 'type') is filled with ->import_read_record called by import.php page before calling import_insert
625  if (empty($newval) && $arrayrecord[($key-1)]['type'] < 0) $listvalues[] = ($newval=='0'?$newval:"null");
626  elseif (empty($newval) && $arrayrecord[($key-1)]['type'] == 0) $listvalues[] = "''";
627  else $listvalues[] = "'".$this->db->escape($newval)."'";
628  }
629  $i++;
630  }
631 
632  // We add hidden fields (but only if there is at least one field to add into table)
633  if (!empty($listfields) && is_array($objimport->array_import_fieldshidden[0]))
634  {
635  // Loop on each hidden fields to add them into listfields/listvalues
636  foreach($objimport->array_import_fieldshidden[0] as $key => $val)
637  {
638  if (! preg_match('/^'.preg_quote($alias).'\./', $key)) continue; // Not a field of current table
639  if ($val == 'user->id')
640  {
641  $listfields[] = preg_replace('/^'.preg_quote($alias).'\./','',$key);
642  $listvalues[] = $user->id;
643  }
644  elseif (preg_match('/^lastrowid-/',$val))
645  {
646  $tmp=explode('-',$val);
647  $lastinsertid=(isset($last_insert_id_array[$tmp[1]]))?$last_insert_id_array[$tmp[1]]:0;
648  $keyfield = preg_replace('/^'.preg_quote($alias).'\./','',$key);
649  $listfields[] = $keyfield;
650  $listvalues[] = $lastinsertid;
651  //print $key."-".$val."-".$listfields."-".$listvalues."<br>";exit;
652  }
653  }
654  }
655  //print 'listfields='.$listfields.'<br>listvalues='.$listvalues.'<br>';
656 
657  // If no error for this $alias/$tablename, we have a complete $listfields and $listvalues that are defined
658  // so we can try to make the insert or update now.
659  if (! $errorforthistable)
660  {
661  //print "$alias/$tablename/$listfields/$listvalues<br>";
662  if (!empty($listfields))
663  {
664  $updatedone = false;
665  $insertdone = false;
666  if (!empty($updatekeys)) {
667  // We do SELECT to get the rowid, if we already have the rowid, it's to be used below for related tables (extrafields)
668 
669  if (empty($lastinsertid)) { // No insert done yet for a parent table
670  $sqlSelect = 'SELECT rowid FROM '.$tablename;
671 
672  $data = array_combine($listfields, $listvalues);
673  $where = array();
674  $filters = array();
675  foreach ($updatekeys as $key) {
676  $col = $objimport->array_import_updatekeys[0][$key];
677  $key=preg_replace('/^.*\./i','',$key);
678  $where[] = $key.' = '.$data[$key];
679  $filters[] = $col.' = '.$data[$key];
680  }
681  $sqlSelect.= ' WHERE '.implode(' AND ', $where);
682 
683  $resql=$this->db->query($sqlSelect);
684  if($resql) {
685  $res = $this->db->fetch_object($resql);
686  if($resql->num_rows == 1) {
687  $lastinsertid = $res->rowid;
688  $last_insert_id_array[$tablename] = $lastinsertid;
689  } else if($resql->num_rows > 1) {
690  $this->errors[$error]['lib']=$langs->trans('MultipleRecordFoundWithTheseFilters', implode($filters, ', '));
691  $this->errors[$error]['type']='SQL';
692  $error++;
693  } else {
694  // No record found with filters, insert will be tried below
695  }
696  }
697  else
698  {
699  //print 'E';
700  $this->errors[$error]['lib']=$this->db->lasterror();
701  $this->errors[$error]['type']='SQL';
702  $error++;
703  }
704  } else {
705  // We have a last INSERT ID. Check if we have a row referencing this foreign key.
706  // This is required when updating table with some extrafields. When inserting a record in parent table, we can make
707  // a direct insert into subtable extrafields, but when me wake an update, the insertid is defined and the child record
708  // may already exists. So we rescan the extrafield table to know if record exists or not for the rowid.
709  // Note: For extrafield tablename, we have in importfieldshidden_array an enty 'extra.fk_object'=>'lastrowid-tableparent' so $keyfield is 'fk_object'
710  $sqlSelect = 'SELECT rowid FROM '.$tablename;
711 
712  if(empty($keyfield)) $keyfield = 'rowid';
713  $sqlSelect .= ' WHERE '.$keyfield.' = '.$lastinsertid;
714 
715  $resql=$this->db->query($sqlSelect);
716  if($resql) {
717  $res = $this->db->fetch_object($resql);
718  if($resql->num_rows == 1) {
719  // We have a row referencing this last foreign key, continue with UPDATE.
720  } else {
721  // No record found referencing this last foreign key,
722  // force $lastinsertid to 0 so we INSERT below.
723  $lastinsertid = 0;
724  }
725  }
726  else
727  {
728  //print 'E';
729  $this->errors[$error]['lib']=$this->db->lasterror();
730  $this->errors[$error]['type']='SQL';
731  $error++;
732  }
733  }
734 
735  if (!empty($lastinsertid)) {
736  // Build SQL UPDATE request
737  $sqlstart = 'UPDATE '.$tablename;
738 
739  $data = array_combine($listfields, $listvalues);
740  $set = array();
741  foreach ($data as $key => $val) {
742  $set[] = $key.' = '.$val;
743  }
744  $sqlstart.= ' SET '.implode(', ', $set);
745 
746  if(empty($keyfield)) $keyfield = 'rowid';
747  $sqlend = ' WHERE '.$keyfield.' = '.$lastinsertid;
748 
749  $sql = $sqlstart.$sqlend;
750 
751  // Run update request
752  $resql=$this->db->query($sql);
753  if($resql) {
754  // No error, update has been done. $this->db->db->affected_rows can be 0 if data hasn't changed
755  $updatedone = true;
756  }
757  else
758  {
759  //print 'E';
760  $this->errors[$error]['lib']=$this->db->lasterror();
761  $this->errors[$error]['type']='SQL';
762  $error++;
763  }
764  }
765  }
766 
767  // Update not done, we do insert
768  if (!$error && !$updatedone) {
769  // Build SQL INSERT request
770  $sqlstart = 'INSERT INTO '.$tablename.'('.implode(', ', $listfields).', import_key';
771  $sqlend = ') VALUES('.implode(', ', $listvalues).", '".$importid."'";
772  if (! empty($tablewithentity_cache[$tablename])) {
773  $sqlstart.= ', entity';
774  $sqlend.= ', '.$conf->entity;
775  }
776  if (! empty($objimport->array_import_tables_creator[0][$alias])) {
777  $sqlstart.= ', '.$objimport->array_import_tables_creator[0][$alias];
778  $sqlend.=', '.$user->id;
779  }
780  $sql = $sqlstart.$sqlend.')';
781  dol_syslog("import_xlsx.modules", LOG_DEBUG);
782 
783  // Run insert request
784  if ($sql)
785  {
786  $resql=$this->db->query($sql);
787  if ($resql)
788  {
789  $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).
790  $insertdone = true;
791  }
792  else
793  {
794  //print 'E';
795  $this->errors[$error]['lib']=$this->db->lasterror();
796  $this->errors[$error]['type']='SQL';
797  $error++;
798  }
799  }
800  }
801  }
802  /*else
803  {
804  dol_print_error('','ErrorFieldListEmptyFor '.$alias."/".$tablename);
805  }*/
806  }
807 
808  if ($error) break;
809  }
810 
811  if($updatedone) $this->nbupdate++;
812  if($insertdone) $this->nbinsert++;
813  }
814 
815  return 1;
816  }
817 }
print $object label
hash of file content (md5_file(dol_osencode($destfull))
Definition: edit.php:153
import_open_file($file)
Open input file.
if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) if(! empty($conf->don->enabled) && $user->rights->societe->lire) if(! empty($conf->tax->enabled) && $user->rights->tax->charges->lire) if(! empty($conf->facture->enabled) &&! empty($conf->commande->enabled) && $user->rights->commande->lire &&empty($conf->global->WORKFLOW_DISABLE_CREATE_INVOICE_FROM_ORDER)) if(! empty($conf->facture->enabled) && $user->rights->facture->lire) if(! empty($conf->fournisseur->enabled) && $user->rights->fournisseur->facture->lire) $resql
Social contributions to pay.
Definition: index.php:1053
import_read_header()
Input header line from file.
Class to import Excel files.
dol_print_error($db='', $error='', $errors=null)
Affiche message erreur system avec toutes les informations pour faciliter le diagnostic et la remonte...
write_footer_example($outputlangs)
Output footer of an example file for this format.
import_get_nb_of_lines($file)
Return nb of records.
$version
Dolibarr version of driver string.
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.
dol_syslog($message, $level=LOG_INFO, $ident=0, $suffixinfilename='', $restricttologhandler='')
Write log message into outputs.
Class to manage third parties objects (customers, suppliers, prospects...)
if(! function_exists('dol_getprefix')) dol_include_once($relpath, $classname='')
Make an include_once using default root and alternate root if it fails.
__construct($db, $datatoimport)
Constructor.
import_insert($arrayrecord, $array_match_file_to_database, $objimport, $maxfields, $importid, $updatekeys)
Insert a record into database.
write_header_example($outputlangs)
Output header of an example file for this format.
write_title_example($outputlangs, $headerlinefields)
Output title line of an example file for this format.
Parent class for import file readers.
import_close_file()
Close file handle.
price2num($amount, $rounding='', $alreadysqlnb=0)
Function that return a number with universal decimal format (decimal separator is &#39;...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.