dolibarr  7.0.0-beta
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@capnetworks.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 {
36  var $db;
37  var $datatoimport;
38 
39  var $error='';
40  var $errors=array();
41 
42  var $id; // Id of driver
43  var $label; // Label of driver
44  var $extension; // Extension of files imported by driver
45  var $version; // Version of driver
46 
47  var $label_lib; // Label of external lib used by driver
48  var $version_lib; // Version of external lib used by driver
49 
50  var $separator;
51 
52  var $file; // Path of file
53  var $handle; // Handle fichier
54 
55  var $cacheconvert=array(); // Array to cache list of value found after a convertion
56  var $cachefieldtable=array(); // Array to cache list of value found into fields@tables
57 
58  var $workbook; // temporary import file
59  var $record; // current record
60  var $headers;
61 
62 
69  function __construct($db,$datatoimport)
70  {
71  global $conf,$langs;
72  $this->db = $db;
73 
74  // this is used as an extension from the example file code, so we have to put xlsx here !!!
75  $this->id='xlsx'; // Same value as xxx in file name export_xxx.modules.php
76  $this->label='Excel 2007'; // Label of driver
77  $this->desc=$langs->trans("Excel2007FormatDesc");
78  $this->extension='xlsx'; // Extension for generated file by this driver
79  $this->picto='mime/xls'; // Picto (This is not used by the example file code as Mime type, too bad ...)
80  $this->version='1.0'; // Driver version
81 
82  // If driver use an external library, put its name here
83  require_once PHPEXCEL_PATH.'PHPExcel.php';
84  require_once PHPEXCEL_PATH.'PHPExcel/Style/Alignment.php';
85  if (! class_exists('ZipArchive')) // For Excel2007, PHPExcel need ZipArchive
86  {
87  $langs->load("errors");
88  $this->error=$langs->trans('ErrorPHPNeedModule','zip');
89  return -1;
90  }
91  $this->label_lib='PhpExcel';
92  $this->version_lib='1.8.0';
93 
94  $this->datatoimport=$datatoimport;
95  if (preg_match('/^societe_/',$datatoimport)) $this->thirpartyobject=new Societe($this->db);
96  }
97 
98 
105  function write_header_example($outputlangs)
106  {
107  global $user,$conf,$langs;
108  // create a temporary object, the final output will be generated in footer
109  if (!empty($conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR)) {
110  $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
111  $cacheSettings = array (
112  'dir' => $conf->global->MAIN_USE_FILECACHE_EXPORT_EXCEL_DIR
113  );
114  PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
115  }
116 
117  $this->workbook = new PHPExcel();
118  $this->workbook->getProperties()->setCreator($user->getFullName($outputlangs).' - Dolibarr '.DOL_VERSION);
119  $this->workbook->getProperties()->setTitle($outputlangs->trans("Import").' - '.$file);
120  $this->workbook->getProperties()->setSubject($outputlangs->trans("Import").' - '.$file);
121  $this->workbook->getProperties()->setDescription($outputlangs->trans("Import").' - '.$file);
122 
123  $this->workbook->setActiveSheetIndex(0);
124  $this->workbook->getActiveSheet()->setTitle($outputlangs->trans("Sheet"));
125  $this->workbook->getActiveSheet()->getDefaultRowDimension()->setRowHeight(16);
126 
127  return '';
128  }
129 
137  function write_title_example($outputlangs,$headerlinefields)
138  {
139  global $conf;
140  $this->workbook->getActiveSheet()->getStyle('1')->getFont()->setBold(true);
141  $this->workbook->getActiveSheet()->getStyle('1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
142 
143  $col = 0;
144  foreach($headerlinefields as $field) {
145  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, 1, $outputlangs->transnoentities($field));
146  // set autowidth
147  //$this->workbook->getActiveSheet()->getColumnDimension($this->column2Letter($col + 1))->setAutoSize(true);
148  $col++;
149  }
150 
151  return ''; // final output will be generated in footer
152  }
153 
161  function write_record_example($outputlangs,$contentlinevalues)
162  {
163  $col = 0;
164  $row = 2;
165  foreach($contentlinevalues as $cell) {
166  $this->workbook->getActiveSheet()->SetCellValueByColumnAndRow($col, $row, $cell);
167  $col++;
168  }
169 
170  return ''; // final output will be generated in footer
171  }
172 
179  function write_footer_example($outputlangs)
180  {
181  // return te file content as a string
182  $tempfile = tempnam(sys_get_temp_dir(), 'dol');
183  $objWriter = new PHPExcel_Writer_Excel2007($this->workbook);
184  $objWriter->save($tempfile);
185  $this->workbook->disconnectWorksheets();
186  unset($this->workbook);
187 
188  $content = file_get_contents($tempfile);
189  unlink($tempfile);
190  return $content;
191  }
192 
193 
194 
201  function import_open_file($file)
202  {
203  global $langs;
204  $ret=1;
205 
206  dol_syslog(get_class($this)."::open_file file=".$file);
207 
208  $reader = new PHPExcel_Reader_Excel2007();
209  $this->workbook = $reader->load($file);
210  $this->record = 1;
211  $this->file = $file;
212 
213  return $ret;
214  }
215 
216 
223  function import_get_nb_of_lines($file)
224  {
225  $reader = new PHPExcel_Reader_Excel2007();
226  $this->workbook = $reader->load($file);
227 
228  $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
229 
230  $this->workbook->disconnectWorksheets();
231  unset($this->workbook);
232 
233  return $rowcount;
234  }
235 
236 
243  {
244  // This is not called by the import code !!!
245  $this->headers = array();
246  $colcount = PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn());
247  for($col=0;$col<$colcount;$col++) {
248  $this->headers[$col] = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, 1)->getValue();
249  }
250  return 0;
251  }
252 
253 
260  {
261  global $conf;
262 
263  $rowcount = $this->workbook->getActiveSheet()->getHighestDataRow();
264  if($this->record > $rowcount)
265  return false;
266  $array = array();
267  $colcount = PHPExcel_Cell::columnIndexFromString($this->workbook->getActiveSheet()->getHighestDataColumn(0));
268  for($col=0;$col<$colcount;$col++) {
269  $val = $this->workbook->getActiveSheet()->getCellByColumnAndRow($col, $this->record)->getValue();
270  $array[$col]['val'] = $val;
271  $array[$col]['type'] = (dol_strlen($val)?1:-1); // If empty we consider it null
272  }
273  $this->record++;
274  return $array;
275  }
276 
282  function import_close_file()
283  {
284  $this->workbook->disconnectWorksheets();
285  unset($this->workbook);
286  }
287 
288 
300  // What is this doing here ? it is common to all imports, is should be in the parent class
301  function import_insert($arrayrecord,$array_match_file_to_database,$objimport,$maxfields,$importid,$updatekeys)
302  {
303  global $langs,$conf,$user;
304  global $thirdparty_static; // Specific to thirdparty import
305  global $tablewithentity_cache; // Cache to avoid to call desc at each rows on tables
306 
307  $error=0;
308  $warning=0;
309  $this->errors=array();
310  $this->warnings=array();
311 
312  //dol_syslog("import_csv.modules maxfields=".$maxfields." importid=".$importid);
313 
314  //var_dump($array_match_file_to_database);
315  //var_dump($arrayrecord);
316  $array_match_database_to_file=array_flip($array_match_file_to_database);
317  $sort_array_match_file_to_database=$array_match_file_to_database;
318  ksort($sort_array_match_file_to_database);
319 
320  //var_dump($sort_array_match_file_to_database);
321 
322  if (count($arrayrecord) == 0 || (count($arrayrecord) == 1 && empty($arrayrecord[0]['val'])))
323  {
324  //print 'W';
325  $this->warnings[$warning]['lib']=$langs->trans('EmptyLine');
326  $this->warnings[$warning]['type']='EMPTY';
327  $warning++;
328  }
329  else
330  {
331  $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)
332  $updatedone = false;
333  $insertdone = false;
334  // For each table to insert, me make a separate insert
335  foreach($objimport->array_import_tables[0] as $alias => $tablename)
336  {
337  // Build sql request
338  $sql='';
339  $listfields=array();
340  $listvalues=array();
341  $i=0;
342  $errorforthistable=0;
343 
344  // Define $tablewithentity_cache[$tablename] if not already defined
345  if (! isset($tablewithentity_cache[$tablename])) // keep this test with "isset"
346  {
347  dol_syslog("Check if table ".$tablename." has an entity field");
348  $resql=$this->db->DDLDescTable($tablename,'entity');
349  if ($resql)
350  {
351  $obj=$this->db->fetch_object($resql);
352  if ($obj) $tablewithentity_cache[$tablename]=1; // table contains entity field
353  else $tablewithentity_cache[$tablename]=0; // table does not contains entity field
354  }
355  else dol_print_error($this->db);
356  }
357  else
358  {
359  //dol_syslog("Table ".$tablename." check for entity into cache is ".$tablewithentity_cache[$tablename]);
360  }
361 
362 
363  // Loop on each fields in the match array: $key = 1..n, $val=alias of field (s.nom)
364  foreach($sort_array_match_file_to_database as $key => $val)
365  {
366  $fieldalias=preg_replace('/\..*$/i','',$val);
367  $fieldname=preg_replace('/^.*\./i','',$val);
368 
369  if ($alias != $fieldalias) continue; // Not a field of current table
370 
371  if ($key <= $maxfields)
372  {
373  // Set $newval with value to insert and set $listvalues with sql request part for insert
374  $newval='';
375  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
376 
377  // Make some tests on $newval
378 
379  // Is it a required field ?
380  if (preg_match('/\*/',$objimport->array_import_fields[0][$val]) && ((string) $newval==''))
381  {
382  $this->errors[$error]['lib']=$langs->trans('ErrorMissingMandatoryValue',$key);
383  $this->errors[$error]['type']='NOTNULL';
384  $errorforthistable++;
385  $error++;
386  }
387  // Test format only if field is not a missing mandatory field (field may be a value or empty but not mandatory)
388  else
389  {
390  // We convert field if required
391  if (! empty($objimport->array_import_convertvalue[0][$val]))
392  {
393  //print 'Must convert '.$newval.' with rule '.join(',',$objimport->array_import_convertvalue[0][$val]).'. ';
394  if ($objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeid'
395  || $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromref'
396  || $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeorlabel'
397  )
398  {
399  // 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.
400  $isidorref='id';
401  if (! is_numeric($newval) && $newval != '' && ! preg_match('/^id:/i',$newval)) $isidorref='ref';
402  $newval=preg_replace('/^(id|ref):/i','',$newval); // Remove id: or ref: that was used to force if field is id or ref
403  //print 'Val is now '.$newval.' and is type '.$isidorref."<br>\n";
404 
405  if ($isidorref == 'ref') // If value into input import file is a ref, we apply the function defined into descriptor
406  {
407  $file=(empty($objimport->array_import_convertvalue[0][$val]['classfile'])?$objimport->array_import_convertvalue[0][$val]['file']:$objimport->array_import_convertvalue[0][$val]['classfile']);
408  $class=$objimport->array_import_convertvalue[0][$val]['class'];
409  $method=$objimport->array_import_convertvalue[0][$val]['method'];
410  if ($this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval] != '')
411  {
412  $newval=$this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval];
413  }
414  else
415  {
416  $resultload = dol_include_once($file);
417  if (empty($resultload))
418  {
419  dol_print_error('', 'Error trying to call file='.$file.', class='.$class.', method='.$method);
420  break;
421  }
422  $classinstance=new $class($this->db);
423  // Try the fetch from code or ref
424  call_user_func_array(array($classinstance, $method),array('', $newval));
425  // If not found, try the fetch from label
426  if (! ($classinstance->id != '') && $objimport->array_import_convertvalue[0][$val]['rule']=='fetchidfromcodeorlabel')
427  {
428  call_user_func_array(array($classinstance, $method),array('', '', $newval));
429  }
430  $this->cacheconvert[$file.'_'.$class.'_'.$method.'_'][$newval]=$classinstance->id;
431  //print 'We have made a '.$class.'->'.$method.' to get id from code '.$newval.'. ';
432  if ($classinstance->id != '') // id may be 0, it is a found value
433  {
434  $newval=$classinstance->id;
435  }
436  else
437  {
438  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']));
439  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']));
440  else $this->errors[$error]['lib']='ErrorFieldValueNotIn';
441  $this->errors[$error]['type']='FOREIGNKEY';
442  $errorforthistable++;
443  $error++;
444  }
445  }
446  }
447 
448  }
449  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='zeroifnull')
450  {
451  if (empty($newval)) $newval='0';
452  }
453  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getcustomercodeifauto')
454  {
455  if (strtolower($newval) == 'auto')
456  {
457  $this->thirpartyobject->get_codeclient(0,0);
458  $newval=$this->thirpartyobject->code_client;
459  //print 'code_client='.$newval;
460  }
461  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
462  }
463  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getsuppliercodeifauto')
464  {
465  if (strtolower($newval) == 'auto')
466  {
467  $newval=$this->thirpartyobject->get_codefournisseur(0,1);
468  $newval=$this->thirpartyobject->code_fournisseur;
469  //print 'code_fournisseur='.$newval;
470  }
471  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
472  }
473  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getcustomeraccountancycodeifauto')
474  {
475  if (strtolower($newval) == 'auto')
476  {
477  $this->thirpartyobject->get_codecompta('customer');
478  $newval=$this->thirpartyobject->code_compta;
479  //print 'code_compta='.$newval;
480  }
481  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
482  }
483  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getsupplieraccountancycodeifauto')
484  {
485  if (strtolower($newval) == 'auto')
486  {
487  $this->thirpartyobject->get_codecompta('supplier');
488  $newval=$this->thirpartyobject->code_compta_fournisseur;
489  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
490  //print 'code_compta_fournisseur='.$newval;
491  }
492  if (empty($newval)) $arrayrecord[($key-1)]['type']=-1; // If we get empty value, we will use "null"
493  }
494  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='getrefifauto')
495  {
496  $defaultref='';
497  // TODO provide the $modTask (module of generation of ref) as parameter of import_insert function
498  $obj = empty($conf->global->PROJECT_TASK_ADDON)?'mod_task_simple':$conf->global->PROJECT_TASK_ADDON;
499  if (! empty($conf->global->PROJECT_TASK_ADDON) && is_readable(DOL_DOCUMENT_ROOT ."/core/modules/project/task/".$conf->global->PROJECT_TASK_ADDON.".php"))
500  {
501  require_once DOL_DOCUMENT_ROOT ."/core/modules/project/task/".$conf->global->PROJECT_TASK_ADDON.'.php';
502  $modTask = new $obj;
503  $defaultref = $modTask->getNextValue(null,null);
504  }
505  if (is_numeric($defaultref) && $defaultref <= 0) $defaultref='';
506  $newval=$defaultref;
507  }
508 
509 
510  elseif ($objimport->array_import_convertvalue[0][$val]['rule']=='numeric')
511  {
512  $newval = price2num($newval);
513  }
514 
515  //print 'Val to use as insert is '.$newval.'<br>';
516  }
517 
518  // Test regexp
519  if (! empty($objimport->array_import_regex[0][$val]) && ($newval != ''))
520  {
521  // If test is "Must exist in a field@table"
522  if (preg_match('/^(.*)@(.*)$/',$objimport->array_import_regex[0][$val],$reg))
523  {
524  $field=$reg[1];
525  $table=$reg[2];
526 
527  // Load content of field@table into cache array
528  if (! is_array($this->cachefieldtable[$field.'@'.$table])) // If content of field@table not already loaded into cache
529  {
530  $sql="SELECT ".$field." as aliasfield FROM ".$table;
531  $resql=$this->db->query($sql);
532  if ($resql)
533  {
534  $num=$this->db->num_rows($resql);
535  $i=0;
536  while ($i < $num)
537  {
538  $obj=$this->db->fetch_object($resql);
539  if ($obj) $this->cachefieldtable[$field.'@'.$table][]=$obj->aliasfield;
540  $i++;
541  }
542  }
543  else
544  {
545  dol_print_error($this->db);
546  }
547  }
548 
549  // Now we check cache is not empty (should not) and key is into cache
550  if (! is_array($this->cachefieldtable[$field.'@'.$table]) || ! in_array($newval,$this->cachefieldtable[$field.'@'.$table]))
551  {
552  $this->errors[$error]['lib']=$langs->transnoentitiesnoconv('ErrorFieldValueNotIn',$key,$newval,$field,$table);
553  $this->errors[$error]['type']='FOREIGNKEY';
554  $errorforthistable++;
555  $error++;
556  }
557  }
558  // If test is just a static regex
559  else if (! preg_match('/'.$objimport->array_import_regex[0][$val].'/i',$newval))
560  {
561  //if ($key == 19) print "xxx".$newval."zzz".$objimport->array_import_regex[0][$val]."<br>";
562  $this->errors[$error]['lib']=$langs->transnoentitiesnoconv('ErrorWrongValueForField',$key,$newval,$objimport->array_import_regex[0][$val]);
563  $this->errors[$error]['type']='REGEX';
564  $errorforthistable++;
565  $error++;
566  }
567  }
568 
569  // Other tests
570  // ...
571  }
572 
573  // Define $listfields and $listvalues to build SQL request
574  $listfields[] = $fieldname;
575 
576  // Note: arrayrecord (and 'type') is filled with ->import_read_record called by import.php page before calling import_insert
577  if (empty($newval) && $arrayrecord[($key-1)]['type'] < 0) $listvalues[] = ($newval=='0'?$newval:"null");
578  elseif (empty($newval) && $arrayrecord[($key-1)]['type'] == 0) $listvalues[] = "''";
579  else $listvalues[] = "'".$this->db->escape($newval)."'";
580  }
581  $i++;
582  }
583 
584  // We add hidden fields (but only if there is at least one field to add into table)
585  if (!empty($listfields) && is_array($objimport->array_import_fieldshidden[0]))
586  {
587  // Loop on each hidden fields to add them into listfields/listvalues
588  foreach($objimport->array_import_fieldshidden[0] as $key => $val)
589  {
590  if (! preg_match('/^'.preg_quote($alias).'\./', $key)) continue; // Not a field of current table
591  if ($val == 'user->id')
592  {
593  $listfields[] = preg_replace('/^'.preg_quote($alias).'\./','',$key);
594  $listvalues[] = $user->id;
595  }
596  elseif (preg_match('/^lastrowid-/',$val))
597  {
598  $tmp=explode('-',$val);
599  $lastinsertid=(isset($last_insert_id_array[$tmp[1]]))?$last_insert_id_array[$tmp[1]]:0;
600  $keyfield = preg_replace('/^'.preg_quote($alias).'\./','',$key);
601  $listfields[] = $keyfield;
602  $listvalues[] = $lastinsertid;
603  //print $key."-".$val."-".$listfields."-".$listvalues."<br>";exit;
604  }
605  }
606  }
607  //print 'listfields='.$listfields.'<br>listvalues='.$listvalues.'<br>';
608 
609  // If no error for this $alias/$tablename, we have a complete $listfields and $listvalues that are defined
610  if (! $errorforthistable)
611  {
612  //print "$alias/$tablename/$listfields/$listvalues<br>";
613  if (!empty($listfields))
614  {
615  $updatedone = false;
616  $insertdone = false;
617  if (!empty($updatekeys)) {
618  // We do SELECT to get the rowid, if we already have the rowid, it's to be used below for related tables (extrafields)
619  if (empty($lastinsertid)) {
620  $sqlSelect = 'SELECT rowid FROM '.$tablename;
621 
622  $data = array_combine($listfields, $listvalues);
623  $where = array();
624  $filters = array();
625  foreach ($updatekeys as $key) {
626  $col = $objimport->array_import_updatekeys[0][$key];
627  $key=preg_replace('/^.*\./i','',$key);
628  $where[] = $key.' = '.$data[$key];
629  $filters[] = $col.' = '.$data[$key];
630  }
631  $sqlSelect.= ' WHERE '.implode(' AND ', $where);
632 
633  $resql=$this->db->query($sqlSelect);
634  if($resql) {
635  $res = $this->db->fetch_object($resql);
636  if($resql->num_rows == 1) {
637  $lastinsertid = $res->rowid;
638  $last_insert_id_array[$tablename] = $lastinsertid;
639  } else if($resql->num_rows > 1) {
640  $this->errors[$error]['lib']=$langs->trans('MultipleRecordFoundWithTheseFilters', implode($filters, ', '));
641  $this->errors[$error]['type']='SQL';
642  $error++;
643  } else {
644  // No record found with filters, insert will be tried below
645  }
646  }
647  else
648  {
649  //print 'E';
650  $this->errors[$error]['lib']=$this->db->lasterror();
651  $this->errors[$error]['type']='SQL';
652  $error++;
653  }
654  }
655 
656  if (!empty($lastinsertid)) {
657  // Build SQL UPDATE request
658  $sqlstart = 'UPDATE '.$tablename;
659 
660  $data = array_combine($listfields, $listvalues);
661  $set = array();
662  foreach ($data as $key => $val) {
663  $set[] = $key.' = '.$val;
664  }
665  $sqlstart.= ' SET '.implode(', ', $set);
666 
667  if(empty($keyfield)) $keyfield = 'rowid';
668  $sqlend = ' WHERE '.$keyfield.' = '.$lastinsertid;
669 
670  $sql = $sqlstart.$sqlend;
671 
672  // Run update request
673  $resql=$this->db->query($sql);
674  if($resql) {
675  // No error, update has been done. $this->db->db->affected_rows can be 0 if data hasn't changed
676  $updatedone = true;
677  }
678  else
679  {
680  //print 'E';
681  $this->errors[$error]['lib']=$this->db->lasterror();
682  $this->errors[$error]['type']='SQL';
683  $error++;
684  }
685  }
686  }
687 
688  // Update not done, we do insert
689  if (!$error && !$updatedone) {
690  // Build SQL INSERT request
691  $sqlstart = 'INSERT INTO '.$tablename.'('.implode(', ', $listfields).', import_key';
692  $sqlend = ') VALUES('.implode(', ', $listvalues).", '".$importid."'";
693  if (! empty($tablewithentity_cache[$tablename])) {
694  $sqlstart.= ', entity';
695  $sqlend.= ', '.$conf->entity;
696  }
697  if (! empty($objimport->array_import_tables_creator[0][$alias])) {
698  $sqlstart.= ', '.$objimport->array_import_tables_creator[0][$alias];
699  $sqlend.=', '.$user->id;
700  }
701  $sql = $sqlstart.$sqlend.')';
702  dol_syslog("import_xlsx.modules", LOG_DEBUG);
703 
704  // Run insert request
705  if ($sql)
706  {
707  $resql=$this->db->query($sql);
708  if ($resql)
709  {
710  $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).
711  $insertdone = true;
712  }
713  else
714  {
715  //print 'E';
716  $this->errors[$error]['lib']=$this->db->lasterror();
717  $this->errors[$error]['type']='SQL';
718  $error++;
719  }
720  }
721  }
722  }
723  /*else
724  {
725  dol_print_error('','ErrorFieldListEmptyFor '.$alias."/".$tablename);
726  }*/
727  }
728 
729  if ($error) break;
730  }
731 
732  if($updatedone) $this->nbupdate++;
733  if($insertdone) $this->nbinsert++;
734  }
735 
736  return 1;
737  }
738 
739 }
import_open_file($file)
Open input file.
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.
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...)
__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.
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:1013
if(!function_exists('dol_getprefix')) dol_include_once($relpath, $classname='')
Return a prefix to use for this Dolibarr instance, for session/cookie names or email id...
import_close_file()
Close file handle.
price2num($amount, $rounding='', $alreadysqlnb=0)
Function that return a number with universal decimal format (decimal separator is '...
dol_strlen($string, $stringencoding='UTF-8')
Make a strlen call.