1<?php 2/* Copyright (C) 2005-2011 Laurent Destailleur <eldy@users.sourceforge.net> 3 * Copyright (C) 2005-2012 Regis Houssin <regis.houssin@inodbox.com> 4 * Copyright (C) 2012 Charles-Fr BENKE <charles.fr@benke.fr> 5 * Copyright (C) 2016 Raphaël Doursenaud <rdoursenaud@gpcsolutions.fr> 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 */ 20 21/** 22 * \file htdocs/exports/class/export.class.php 23 * \ingroup export 24 * \brief File of class to manage exports 25 */ 26 27 28/** 29 * Class to manage exports 30 */ 31class Export 32{ 33 /** 34 * @var DoliDB Database handler. 35 */ 36 public $db; 37 38 public $error; 39 40 public $array_export_code = array(); // Tableau de "idmodule_numexportprofile" 41 public $array_export_code_for_sort = array(); // Tableau de "idmodule_numexportprofile" 42 public $array_export_module = array(); // Tableau de "nom de modules" 43 public $array_export_label = array(); // Tableau de "libelle de lots" 44 public $array_export_sql_start = array(); // Tableau des "requetes sql" 45 public $array_export_sql_end = array(); // Tableau des "requetes sql" 46 public $array_export_sql_order = array(); // Tableau des "requetes sql" 47 48 public $array_export_fields = array(); // Tableau des listes de champ+libelle a exporter 49 public $array_export_TypeFields = array(); // Tableau des listes de champ+Type de filtre 50 public $array_export_FilterValue = array(); // Tableau des listes de champ+Valeur a filtrer 51 public $array_export_entities = array(); // Tableau des listes de champ+alias a exporter 52 public $array_export_dependencies = array(); // array of list of entities that must take care of the DISTINCT if a field is added into export 53 public $array_export_special = array(); // array of special operations to do on field 54 public $array_export_examplevalues = array(); // array with examples for fields 55 public $array_export_help = array(); // array with tooltip help for fields 56 57 // To store export modules 58 public $hexa; // List of fields in the export profile 59 public $hexafiltervalue; // List of search criteria in the export profile 60 public $datatoexport; 61 public $model_name; // Name of export profile 62 63 public $sqlusedforexport; 64 65 66 /** 67 * Constructor 68 * 69 * @param DoliDB $db Database handler 70 */ 71 public function __construct($db) 72 { 73 $this->db = $db; 74 } 75 76 77 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 78 /** 79 * Load an exportable dataset 80 * 81 * @param User $user Object user making export 82 * @param string $filter Load a particular dataset only 83 * @return int <0 if KO, >0 if OK 84 */ 85 public function load_arrays($user, $filter = '') 86 { 87 // phpcs:enable 88 global $langs, $conf, $mysoc; 89 90 dol_syslog(get_class($this)."::load_arrays user=".$user->id." filter=".$filter); 91 92 $i = 0; 93 94 // Define list of modules directories into modulesdir 95 require_once DOL_DOCUMENT_ROOT.'/core/lib/functions2.lib.php'; 96 97 $modulesdir = dolGetModulesDirs(); 98 99 foreach ($modulesdir as $dir) 100 { 101 // Search available exports 102 $handle = @opendir(dol_osencode($dir)); 103 if (is_resource($handle)) 104 { 105 // Search module files 106 while (($file = readdir($handle)) !== false) 107 { 108 $reg = array(); 109 if (is_readable($dir.$file) && preg_match("/^(mod.*)\.class\.php$/i", $file, $reg)) 110 { 111 $modulename = $reg[1]; 112 113 // Defined if module is enabled 114 $enabled = true; 115 $part = strtolower(preg_replace('/^mod/i', '', $modulename)); 116 if ($part == 'propale') $part = 'propal'; 117 if (empty($conf->$part->enabled)) $enabled = false; 118 119 if ($enabled) 120 { 121 // Loading Class 122 $file = $dir.$modulename.".class.php"; 123 $classname = $modulename; 124 require_once $file; 125 $module = new $classname($this->db); 126 127 if (isset($module->export_code) && is_array($module->export_code)) 128 { 129 foreach ($module->export_code as $r => $value) 130 { 131 //print $i.'-'.$filter.'-'.$modulename.'-'.join(',',$module->export_code).'<br>'; 132 if ($filter && ($filter != $module->export_code[$r])) continue; 133 134 // Test if condition to show are ok 135 if (!empty($module->export_enabled[$r]) && !verifCond($module->export_enabled[$r])) continue; 136 137 // Test if permissions are ok 138 $bool = true; 139 if (isset($module->export_permission)) 140 { 141 foreach ($module->export_permission[$r] as $val) 142 { 143 $perm = $val; 144 //print_r("$perm[0]-$perm[1]-$perm[2]<br>"); 145 if (!empty($perm[2])) { 146 $bool = isset($user->rights->{$perm[0]}->{$perm[1]}->{$perm[2]}) ? $user->rights->{$perm[0]}->{$perm[1]}->{$perm[2]} : false; 147 } else { 148 $bool = isset($user->rights->{$perm[0]}->{$perm[1]}) ? $user->rights->{$perm[0]}->{$perm[1]} : false; 149 } 150 if ($perm[0] == 'user' && $user->admin) $bool = true; 151 if (!$bool) break; 152 } 153 } 154 //print $bool." $perm[0]"."<br>"; 155 156 // Permissions ok 157 // if ($bool) 158 // { 159 // Charge fichier lang en rapport 160 $langtoload = $module->getLangFilesArray(); 161 if (is_array($langtoload)) 162 { 163 foreach ($langtoload as $key) 164 { 165 $langs->load($key); 166 } 167 } 168 169 170 // Module 171 $this->array_export_module[$i] = $module; 172 // Permission 173 $this->array_export_perms[$i] = $bool; 174 // Icon 175 $this->array_export_icon[$i] = (isset($module->export_icon[$r]) ? $module->export_icon[$r] : $module->picto); 176 // Code du dataset export 177 $this->array_export_code[$i] = $module->export_code[$r]; 178 // Define a key for sort 179 $this->array_export_code_for_sort[$i] = $module->module_position.'_'.$module->export_code[$r]; // Add a key into the module 180 // Libelle du dataset export 181 $this->array_export_label[$i] = $module->getExportDatasetLabel($r); 182 // Tableau des champ a exporter (cle=champ, valeur=libelle) 183 $this->array_export_fields[$i] = $module->export_fields_array[$r]; 184 // Tableau des champs a filtrer (cle=champ, valeur1=type de donnees) on verifie que le module a des filtres 185 $this->array_export_TypeFields[$i] = (isset($module->export_TypeFields_array[$r]) ? $module->export_TypeFields_array[$r] : ''); 186 // Tableau des entites a exporter (cle=champ, valeur=entite) 187 $this->array_export_entities[$i] = $module->export_entities_array[$r]; 188 // Tableau des entites qui requiert abandon du DISTINCT (cle=entite, valeur=champ id child records) 189 $this->array_export_dependencies[$i] = (!empty($module->export_dependencies_array[$r]) ? $module->export_dependencies_array[$r] : ''); 190 // Tableau des operations speciales sur champ 191 $this->array_export_special[$i] = (!empty($module->export_special_array[$r]) ? $module->export_special_array[$r] : ''); 192 // Array of examples 193 $this->array_export_examplevalues[$i] = (!empty($module->export_examplevalues_array[$r]) ? $module->export_examplevalues_array[$r] : null); 194 // Array of help tooltips 195 $this->array_export_help[$i] = (!empty($module->export_help_array[$r]) ? $module->export_help_array[$r] : ''); 196 197 // Requete sql du dataset 198 $this->array_export_sql_start[$i] = $module->export_sql_start[$r]; 199 $this->array_export_sql_end[$i] = $module->export_sql_end[$r]; 200 $this->array_export_sql_order[$i] = (!empty($module->export_sql_order[$r]) ? $module->export_sql_order[$r] : null); 201 //$this->array_export_sql[$i]=$module->export_sql[$r]; 202 203 dol_syslog(get_class($this)."::load_arrays loaded for module ".$modulename." with index ".$i.", dataset=".$module->export_code[$r].", nb of fields=".(!empty($module->export_fields_code[$r]) ?count($module->export_fields_code[$r]) : '')); 204 $i++; 205 // } 206 } 207 } 208 } 209 } 210 } 211 closedir($handle); 212 } 213 } 214 215 return 1; 216 } 217 218 219 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 220 /** 221 * Build the sql export request. 222 * Arrays this->array_export_xxx are already loaded for required datatoexport 223 * 224 * @param int $indice Indice of export 225 * @param array $array_selected Filter fields on array of fields to export 226 * @param array $array_filterValue Filter records on array of value for fields 227 * @return string SQL String. Example "select s.rowid as r_rowid, s.status as s_status from ..." 228 */ 229 public function build_sql($indice, $array_selected, $array_filterValue) 230 { 231 // phpcs:enable 232 // Build the sql request 233 $sql = $this->array_export_sql_start[$indice]; 234 $i = 0; 235 236 //print_r($array_selected); 237 foreach ($this->array_export_fields[$indice] as $key => $value) 238 { 239 if (!array_key_exists($key, $array_selected)) continue; // Field not selected 240 if (preg_match('/^none\./', $key)) continue; // A field that must not appears into SQL 241 if ($i > 0) $sql .= ', '; 242 else $i++; 243 244 if (strpos($key, ' as ') === false) { 245 $newfield = $key.' as '.str_replace(array('.', '-', '(', ')'), '_', $key); 246 } else { 247 $newfield = $key; 248 } 249 250 $sql .= $newfield; 251 } 252 $sql .= $this->array_export_sql_end[$indice]; 253 254 // Add the WHERE part. Filtering into sql if a filtering array is provided 255 if (is_array($array_filterValue) && !empty($array_filterValue)) 256 { 257 $sqlWhere = ''; 258 // Loop on each condition to add 259 foreach ($array_filterValue as $key => $value) 260 { 261 if (preg_match('/GROUP_CONCAT/i', $key)) continue; 262 if ($value != '') $sqlWhere .= " and ".$this->build_filterQuery($this->array_export_TypeFields[$indice][$key], $key, $array_filterValue[$key]); 263 } 264 $sql .= $sqlWhere; 265 } 266 267 // Add the order 268 $sql .= $this->array_export_sql_order[$indice]; 269 270 // Add the HAVING part. 271 if (is_array($array_filterValue) && !empty($array_filterValue)) 272 { 273 // Loop on each condition to add 274 foreach ($array_filterValue as $key => $value) 275 { 276 if (preg_match('/GROUP_CONCAT/i', $key) and $value != '') $sql .= " HAVING ".$this->build_filterQuery($this->array_export_TypeFields[$indice][$key], $key, $array_filterValue[$key]); 277 } 278 } 279 280 return $sql; 281 } 282 283 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 284 /** 285 * Build the conditionnal string from filter the query 286 * 287 * @param string $TypeField Type of Field to filter 288 * @param string $NameField Name of the field to filter 289 * @param string $ValueField Value of the field for filter. Must not be '' 290 * @return string sql string of then field ex : "field='xxx'>" 291 */ 292 public function build_filterQuery($TypeField, $NameField, $ValueField) 293 { 294 // phpcs:enable 295 //print $TypeField." ".$NameField." ".$ValueField; 296 $InfoFieldList = explode(":", $TypeField); 297 // build the input field on depend of the type of file 298 switch ($InfoFieldList[0]) { 299 case 'Text': 300 if (!(strpos($ValueField, '%') === false)) 301 $szFilterQuery .= " ".$NameField." LIKE '".$ValueField."'"; 302 else $szFilterQuery .= " ".$NameField." = '".$ValueField."'"; 303 break; 304 case 'Date': 305 if (strpos($ValueField, "+") > 0) 306 { 307 // mode plage 308 $ValueArray = explode("+", $ValueField); 309 $szFilterQuery = "(".$this->conditionDate($NameField, trim($ValueArray[0]), ">="); 310 $szFilterQuery .= " AND ".$this->conditionDate($NameField, trim($ValueArray[1]), "<=").")"; 311 } else { 312 if (is_numeric(substr($ValueField, 0, 1))) 313 $szFilterQuery = $this->conditionDate($NameField, trim($ValueField), "="); 314 else $szFilterQuery = $this->conditionDate($NameField, trim(substr($ValueField, 1)), substr($ValueField, 0, 1)); 315 } 316 break; 317 case 'Duree': 318 break; 319 case 'Numeric': 320 // si le signe - 321 if (strpos($ValueField, "+") > 0) 322 { 323 // mode plage 324 $ValueArray = explode("+", $ValueField); 325 $szFilterQuery = "(".$NameField.">=".$ValueArray[0]; 326 $szFilterQuery .= " AND ".$NameField."<=".$ValueArray[1].")"; 327 } else { 328 if (is_numeric(substr($ValueField, 0, 1))) 329 $szFilterQuery = " ".$NameField."=".$ValueField; 330 else $szFilterQuery = " ".$NameField.substr($ValueField, 0, 1).substr($ValueField, 1); 331 } 332 break; 333 case 'Boolean': 334 $szFilterQuery = " ".$NameField."=".(is_numeric($ValueField) ? $ValueField : ($ValueField == 'yes' ? 1 : 0)); 335 break; 336 case 'Status': 337 case 'List': 338 if (is_numeric($ValueField)) 339 $szFilterQuery = " ".$NameField."=".$ValueField; 340 else { 341 if (!(strpos($ValueField, '%') === false)) 342 $szFilterQuery = " ".$NameField." LIKE '".$ValueField."'"; 343 else $szFilterQuery = " ".$NameField." = '".$ValueField."'"; 344 } 345 break; 346 default: 347 dol_syslog("Error we try to forge an sql export request with a condition on a field with type ".$InfoFieldList[0]." (defined into module descriptor) but this type is unknown/not supported. It looks like a bug into module descriptor.", LOG_ERR); 348 } 349 350 return $szFilterQuery; 351 } 352 353 /** 354 * conditionDate 355 * 356 * @param string $Field Field operand 1 357 * @param string $Value Value operand 2 358 * @param string $Sens Comparison operator 359 * @return string 360 */ 361 public function conditionDate($Field, $Value, $Sens) 362 { 363 // TODO date_format is forbidden, not performant and not portable. Use instead BETWEEN 364 if (strlen($Value) == 4) $Condition = " date_format(".$Field.",'%Y') ".$Sens." '".$Value."'"; 365 elseif (strlen($Value) == 6) $Condition = " date_format(".$Field.",'%Y%m') ".$Sens." '".$Value."'"; 366 else $Condition = " date_format(".$Field.",'%Y%m%d') ".$Sens." ".$Value; 367 return $Condition; 368 } 369 370 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 371 /** 372 * Build an input field used to filter the query 373 * 374 * @param string $TypeField Type of Field to filter. Example: Text, Date, List:c_country:label:rowid, List:c_stcom:label:code, Numeric or Number, Boolean 375 * @param string $NameField Name of the field to filter 376 * @param string $ValueField Initial value of the field to filter 377 * @return string html string of the input field ex : "<input type=text name=... value=...>" 378 */ 379 public function build_filterField($TypeField, $NameField, $ValueField) 380 { 381 // phpcs:enable 382 global $conf, $langs; 383 384 $szFilterField = ''; 385 $InfoFieldList = explode(":", $TypeField); 386 387 // build the input field on depend of the type of file 388 switch ($InfoFieldList[0]) 389 { 390 case 'Text': 391 case 'Date': 392 $szFilterField = '<input type="text" name="'.$NameField.'" value="'.$ValueField.'">'; 393 break; 394 case 'Duree': 395 case 'Numeric': 396 case 'Number': 397 // Must be a string text to allow to use comparison strings like "<= 999" 398 $szFilterField = '<input type="text" size="6" name="'.$NameField.'" value="'.$ValueField.'">'; 399 break; 400 case 'Status': 401 $szFilterField = '<input type="number" size="6" name="'.$NameField.'" value="'.$ValueField.'">'; 402 break; 403 case 'Boolean': 404 $szFilterField = '<select name="'.$NameField.'" class="flat">'; 405 $szFilterField .= '<option '; 406 if ($ValueField == '') $szFilterField .= ' selected '; 407 $szFilterField .= ' value=""> </option>'; 408 409 $szFilterField .= '<option '; 410 if ($ValueField == 'yes' || $ValueField == '1') $szFilterField .= ' selected '; 411 $szFilterField .= ' value="1">'.yn(1).'</option>'; 412 413 $szFilterField .= '<option '; 414 if ($ValueField == 'no' || $ValueField == '0') $szFilterField .= ' selected '; 415 $szFilterField .= ' value="0">'.yn(0).'</option>'; 416 $szFilterField .= "</select>"; 417 break; 418 case 'List': 419 // 0 : Type du champ 420 // 1 : Nom de la table 421 // 2 : Nom du champ contenant le libelle 422 // 3 : Name of field with key (if it is not "rowid"). Used this field as key for combo list. 423 // 4 : Name of element for getEntity(). 424 425 if (!empty($InfoFieldList[3])) 426 $keyList = $InfoFieldList[3]; 427 else $keyList = 'rowid'; 428 $sql = 'SELECT '.$keyList.' as rowid, '.$InfoFieldList[2].' as label'.(empty($InfoFieldList[3]) ? '' : ', '.$InfoFieldList[3].' as code'); 429 if ($InfoFieldList[1] == 'c_stcomm') $sql = 'SELECT id as id, '.$keyList.' as rowid, '.$InfoFieldList[2].' as label'.(empty($InfoFieldList[3]) ? '' : ', '.$InfoFieldList[3].' as code'); 430 if ($InfoFieldList[1] == 'c_country') $sql = 'SELECT '.$keyList.' as rowid, '.$InfoFieldList[2].' as label, code as code'; 431 $sql .= ' FROM '.MAIN_DB_PREFIX.$InfoFieldList[1]; 432 if (!empty($InfoFieldList[4])) { 433 $sql .= ' WHERE entity IN ('.getEntity($InfoFieldList[4]).')'; 434 } 435 436 $resql = $this->db->query($sql); 437 if ($resql) 438 { 439 $szFilterField = '<select class="flat" name="'.$NameField.'">'; 440 $szFilterField .= '<option value="0"> </option>'; 441 $num = $this->db->num_rows($resql); 442 443 $i = 0; 444 if ($num) 445 { 446 while ($i < $num) 447 { 448 $obj = $this->db->fetch_object($resql); 449 if ($obj->label == '-') 450 { 451 // Discard entry '-' 452 $i++; 453 continue; 454 } 455 //var_dump($InfoFieldList[1]); 456 $labeltoshow = dol_trunc($obj->label, 18); 457 if ($InfoFieldList[1] == 'c_stcomm') 458 { 459 $langs->load("companies"); 460 $labeltoshow = (($langs->trans("StatusProspect".$obj->id) != "StatusProspect".$obj->id) ? $langs->trans("StatusProspect".$obj->id) : $obj->label); 461 } 462 if ($InfoFieldList[1] == 'c_country') 463 { 464 //var_dump($sql); 465 $langs->load("dict"); 466 $labeltoshow = (($langs->trans("Country".$obj->code) != "Country".$obj->code) ? $langs->trans("Country".$obj->code) : $obj->label); 467 } 468 if (!empty($ValueField) && $ValueField == $obj->rowid) 469 { 470 $szFilterField .= '<option value="'.$obj->rowid.'" selected>'.$labeltoshow.'</option>'; 471 } else { 472 $szFilterField .= '<option value="'.$obj->rowid.'" >'.$labeltoshow.'</option>'; 473 } 474 $i++; 475 } 476 } 477 $szFilterField .= "</select>"; 478 479 $this->db->free($resql); 480 } else dol_print_error($this->db); 481 break; 482 } 483 484 return $szFilterField; 485 } 486 487 /** 488 * Build an input field used to filter the query 489 * 490 * @param string $TypeField Type of Field to filter 491 * @return string html string of the input field ex : "<input type=text name=... value=...>" 492 */ 493 public function genDocFilter($TypeField) 494 { 495 global $langs; 496 497 $szMsg = ''; 498 $InfoFieldList = explode(":", $TypeField); 499 // build the input field on depend of the type of file 500 switch ($InfoFieldList[0]) { 501 case 'Text': 502 $szMsg = $langs->trans('ExportStringFilter'); 503 break; 504 case 'Date': 505 $szMsg = $langs->trans('ExportDateFilter'); 506 break; 507 case 'Duree': 508 break; 509 case 'Numeric': 510 $szMsg = $langs->trans('ExportNumericFilter'); 511 break; 512 case 'Boolean': 513 break; 514 case 'List': 515 break; 516 } 517 return $szMsg; 518 } 519 520 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 521 /** 522 * Build export file. 523 * File is built into directory $conf->export->dir_temp.'/'.$user->id 524 * Arrays this->array_export_xxx are already loaded for required datatoexport 525 * 526 * @param User $user User that export 527 * @param string $model Export format 528 * @param string $datatoexport Name of dataset to export 529 * @param array $array_selected Filter on array of fields to export 530 * @param array $array_filterValue Filter on array of fields with a filter 531 * @param string $sqlquery If set, transmit the sql request for select (otherwise, sql request is generated from arrays) 532 * @return int <0 if KO, >0 if OK 533 */ 534 public function build_file($user, $model, $datatoexport, $array_selected, $array_filterValue, $sqlquery = '') 535 { 536 // phpcs:enable 537 global $conf, $langs, $mysoc; 538 539 $indice = 0; 540 asort($array_selected); 541 542 dol_syslog(__METHOD__." ".$model.", ".$datatoexport.", ".implode(",", $array_selected)); 543 544 // Check parameters or context properties 545 if (empty($this->array_export_fields) || !is_array($this->array_export_fields)) 546 { 547 $this->error = "ErrorBadParameter"; 548 dol_syslog($this->error, LOG_ERR); 549 return -1; 550 } 551 552 // Creation of class to export using model ExportXXX 553 $dir = DOL_DOCUMENT_ROOT."/core/modules/export/"; 554 $file = "export_".$model.".modules.php"; 555 $classname = "Export".$model; 556 require_once $dir.$file; 557 $objmodel = new $classname($this->db); 558 559 if (!empty($sqlquery)) $sql = $sqlquery; 560 else { 561 // Define value for indice from $datatoexport 562 $foundindice = 0; 563 foreach ($this->array_export_code as $key => $dataset) 564 { 565 if ($datatoexport == $dataset) 566 { 567 $indice = $key; 568 $foundindice++; 569 //print "Found indice = ".$indice." for dataset=".$datatoexport."\n"; 570 break; 571 } 572 } 573 if (empty($foundindice)) 574 { 575 $this->error = "ErrorBadParameter can't find dataset ".$datatoexport." into preload arrays this->array_export_code"; 576 return -1; 577 } 578 $sql = $this->build_sql($indice, $array_selected, $array_filterValue); 579 } 580 581 // Run the sql 582 $this->sqlusedforexport = $sql; 583 dol_syslog(__METHOD__, LOG_DEBUG); 584 $resql = $this->db->query($sql); 585 if ($resql) 586 { 587 //$this->array_export_label[$indice] 588 if (!empty($conf->global->EXPORT_PREFIX_SPEC)) { 589 $filename = $conf->global->EXPORT_PREFIX_SPEC."_".$datatoexport; 590 } 591 else { 592 $filename = "export_".$datatoexport; 593 } 594 $filename .= '.'.$objmodel->getDriverExtension(); 595 $dirname = $conf->export->dir_temp.'/'.$user->id; 596 597 $outputlangs = clone $langs; // We clone to have an object we can modify (for example to change output charset by csv handler) without changing original value 598 599 // Open file 600 dol_mkdir($dirname); 601 $result = $objmodel->open_file($dirname."/".$filename, $outputlangs); 602 603 if ($result >= 0) 604 { 605 // Genere en-tete 606 $objmodel->write_header($outputlangs); 607 608 // Genere ligne de titre 609 $objmodel->write_title($this->array_export_fields[$indice], $array_selected, $outputlangs, isset($this->array_export_TypeFields[$indice]) ? $this->array_export_TypeFields[$indice] : null); 610 611 while ($obj = $this->db->fetch_object($resql)) 612 { 613 // Process special operations 614 if (!empty($this->array_export_special[$indice])) 615 { 616 foreach ($this->array_export_special[$indice] as $key => $value) 617 { 618 if (!array_key_exists($key, $array_selected)) continue; // Field not selected 619 // Operation NULLIFNEG 620 if ($this->array_export_special[$indice][$key] == 'NULLIFNEG') 621 { 622 //$alias=$this->array_export_alias[$indice][$key]; 623 $alias = str_replace(array('.', '-', '(', ')'), '_', $key); 624 if ($obj->$alias < 0) $obj->$alias = ''; 625 } 626 // Operation ZEROIFNEG 627 elseif ($this->array_export_special[$indice][$key] == 'ZEROIFNEG') 628 { 629 //$alias=$this->array_export_alias[$indice][$key]; 630 $alias = str_replace(array('.', '-', '(', ')'), '_', $key); 631 if ($obj->$alias < 0) $obj->$alias = '0'; 632 } 633 // Operation GETNUMOPENDAYS (for Holiday module) 634 elseif ($this->array_export_special[$indice][$key] == 'getNumOpenDays') 635 { 636 include_once DOL_DOCUMENT_ROOT.'/core/lib/date.lib.php'; 637 //$alias=$this->array_export_alias[$indice][$key]; 638 $alias = str_replace(array('.', '-', '(', ')'), '_', $key); 639 $obj->$alias = num_open_day(dol_stringtotime($obj->d_date_debut, 1), dol_stringtotime($obj->d_date_fin, 1), 0, 1, $obj->d_halfday, $mysoc->country_code); 640 } 641 // Operation INVOICEREMAINTOPAY 642 elseif ($this->array_export_special[$indice][$key] == 'getRemainToPay') 643 { 644 //$alias=$this->array_export_alias[$indice][$key]; 645 $alias = str_replace(array('.', '-', '(', ')'), '_', $key); 646 $remaintopay = ''; 647 if ($obj->f_rowid > 0) 648 { 649 global $tmpobjforcomputecall; 650 if (!is_object($tmpobjforcomputecall)) 651 { 652 include_once DOL_DOCUMENT_ROOT.'/compta/facture/class/facture.class.php'; 653 $tmpobjforcomputecall = new Facture($this->db); 654 } 655 $tmpobjforcomputecall->id = $obj->f_rowid; 656 $tmpobjforcomputecall->total_ttc = $obj->f_total_ttc; 657 $tmpobjforcomputecall->close_code = $obj->f_close_code; 658 $remaintopay = $tmpobjforcomputecall->getRemainToPay(); 659 } 660 $obj->$alias = $remaintopay; 661 } else { 662 // TODO FIXME 663 // Export of compute field does not work. $obj contains $obj->alias_field and formula may contains $obj->field 664 // Also the formula may contains objects of class that are not loaded. 665 $computestring = $this->array_export_special[$indice][$key]; 666 //$tmp = dol_eval($computestring, 1, 0); 667 //$obj->$alias = $tmp; 668 669 $this->error = "ERROPNOTSUPPORTED. Operation ".$computestring." not supported. Export of 'computed' extrafields is not yet supported, please remove field."; 670 return -1; 671 } 672 } 673 } 674 // end of special operation processing 675 $objmodel->write_record($array_selected, $obj, $outputlangs, isset($this->array_export_TypeFields[$indice]) ? $this->array_export_TypeFields[$indice] : null); 676 } 677 678 // Genere en-tete 679 $objmodel->write_footer($outputlangs); 680 681 // Close file 682 $objmodel->close_file(); 683 684 return 1; 685 } else { 686 $this->error = $objmodel->error; 687 dol_syslog("Export::build_file Error: ".$this->error, LOG_ERR); 688 return -1; 689 } 690 } else { 691 $this->error = $this->db->error()." - sql=".$sql; 692 return -1; 693 } 694 } 695 696 /** 697 * Save an export model in database 698 * 699 * @param User $user Object user that save 700 * @return int <0 if KO, >0 if OK 701 */ 702 public function create($user) 703 { 704 global $conf; 705 706 dol_syslog("Export.class.php::create"); 707 708 $this->db->begin(); 709 710 $filter = ''; 711 712 $sql = 'INSERT INTO '.MAIN_DB_PREFIX.'export_model ('; 713 $sql .= 'label,'; 714 $sql .= 'type,'; 715 $sql .= 'field,'; 716 $sql .= 'fk_user,'; 717 $sql .= 'filter'; 718 $sql .= ') VALUES ('; 719 $sql .= "'".$this->db->escape($this->model_name)."',"; 720 $sql .= " '".$this->db->escape($this->datatoexport)."',"; 721 $sql .= " '".$this->db->escape($this->hexa)."',"; 722 $sql .= ' '.($user->id > 0 ? $user->id : 'null').","; 723 $sql .= " '".$this->db->escape($this->hexafiltervalue)."'"; 724 $sql .= ")"; 725 726 dol_syslog(get_class($this)."::create", LOG_DEBUG); 727 $resql = $this->db->query($sql); 728 if ($resql) 729 { 730 $this->db->commit(); 731 return 1; 732 } else { 733 $this->error = $this->db->lasterror(); 734 $this->errno = $this->db->lasterrno(); 735 $this->db->rollback(); 736 return -1; 737 } 738 } 739 740 /** 741 * Load an export profil from database 742 * 743 * @param int $id Id of profil to load 744 * @return int <0 if KO, >0 if OK 745 */ 746 public function fetch($id) 747 { 748 $sql = 'SELECT em.rowid, em.label, em.type, em.field, em.filter'; 749 $sql .= ' FROM '.MAIN_DB_PREFIX.'export_model as em'; 750 $sql .= ' WHERE em.rowid = '.((int) $id); 751 752 dol_syslog("Export::fetch", LOG_DEBUG); 753 $result = $this->db->query($sql); 754 if ($result) 755 { 756 $obj = $this->db->fetch_object($result); 757 if ($obj) 758 { 759 $this->id = $obj->rowid; 760 $this->model_name = $obj->label; 761 $this->datatoexport = $obj->type; 762 763 $this->hexa = $obj->field; 764 $this->hexafiltervalue = $obj->filter; 765 766 return 1; 767 } else { 768 $this->error = "ModelNotFound"; 769 return -2; 770 } 771 } else { 772 dol_print_error($this->db); 773 return -3; 774 } 775 } 776 777 778 /** 779 * Delete object in database 780 * 781 * @param User $user User that delete 782 * @param int $notrigger 0=launch triggers after, 1=disable triggers 783 * @return int <0 if KO, >0 if OK 784 */ 785 public function delete($user, $notrigger = 0) 786 { 787 global $conf, $langs; 788 $error = 0; 789 790 $sql = "DELETE FROM ".MAIN_DB_PREFIX."export_model"; 791 $sql .= " WHERE rowid=".$this->id; 792 793 $this->db->begin(); 794 795 dol_syslog(get_class($this)."::delete", LOG_DEBUG); 796 $resql = $this->db->query($sql); 797 if (!$resql) { $error++; $this->errors[] = "Error ".$this->db->lasterror(); } 798 799 // Commit or rollback 800 if ($error) 801 { 802 foreach ($this->errors as $errmsg) 803 { 804 dol_syslog(get_class($this)."::delete ".$errmsg, LOG_ERR); 805 $this->error .= ($this->error ? ', '.$errmsg : $errmsg); 806 } 807 $this->db->rollback(); 808 return -1 * $error; 809 } else { 810 $this->db->commit(); 811 return 1; 812 } 813 } 814 815 // phpcs:disable PEAR.NamingConventions.ValidFunctionName.ScopeNotCamelCaps 816 /** 817 * Output list all export models 818 * TODO Move this into a class htmlxxx.class.php 819 * 820 * @return void 821 */ 822 public function list_export_model() 823 { 824 // phpcs:enable 825 global $conf, $langs; 826 827 $sql = "SELECT em.rowid, em.field, em.label, em.type, em.filter"; 828 $sql .= " FROM ".MAIN_DB_PREFIX."export_model as em"; 829 $sql .= " ORDER BY rowid"; 830 831 $result = $this->db->query($sql); 832 if ($result) 833 { 834 $num = $this->db->num_rows($result); 835 $i = 0; 836 while ($i < $num) 837 { 838 $obj = $this->db->fetch_object($result); 839 $keyModel = array_search($obj->type, $this->array_export_code); 840 print "<tr>"; 841 print '<td><a href=export.php?step=2&action=select_model&exportmodelid='.$obj->rowid.'&datatoexport='.$obj->type.'>'.$obj->label.'</a></td>'; 842 print '<td>'; 843 print img_object($this->array_export_module[$keyModel]->getName(), $this->array_export_icon[$keyModel]).' '; 844 print $this->array_export_module[$keyModel]->getName().' - '; 845 // recuperation du nom de l'export 846 847 $string = $langs->trans($this->array_export_label[$keyModel]); 848 print ($string != $this->array_export_label[$keyModel] ? $string : $this->array_export_label[$keyModel]); 849 print '</td>'; 850 //print '<td>'.$obj->type.$keyModel.'</td>'; 851 print '<td>'.str_replace(',', ' , ', $obj->field).'</td>'; 852 if (!empty($obj->filter)) { 853 $filter = json_decode($obj->filter, true); 854 print '<td>'.str_replace(',', ' , ', $filter['field']).'</td>'; 855 print '<td>'.str_replace(',', ' , ', $filter['value']).'</td>'; 856 } 857 // suppression de l'export 858 print '<td class="right">'; 859 print '<a href="'.$_SERVER["PHP_SELF"].'?action=deleteprof&token='.newToken().'&id='.$obj->rowid.'">'; 860 print img_delete(); 861 print '</a>'; 862 print "</tr>"; 863 864 $i++; 865 } 866 } else { 867 dol_print_error($this->db); 868 } 869 } 870} 871