1<?php 2/** 3 * eGroupWare - importexport 4 * General Comma Serperated Values (CSV) record importer (abstract class) 5 * 6 * @license http://opensource.org/licenses/gpl-license.php GPL - GNU General Public License 7 * @package importexport 8 * @link http://www.egroupware.org 9 * @author Cornelius Weiss <nelius@cwtech.de> 10 * @copyright Cornelius Weiss <nelius@cwtech.de> 11 * @version $Id$ 12 */ 13 14use EGroupware\Api; 15use EGroupware\Api\Link; 16 17/** 18 * class import_csv 19 * This a an abstract implementation of interface iface_import_record 20 * An record is e.g. a single address or or single event. 21 * No mater where the records come from, at the end the get_record method comes out 22 * @todo Throw away spechial chars and trim() entries ? 23 * @todo Check for XSS like userinput! (see common_functions) 24 */ 25class importexport_import_csv implements importexport_iface_import_record { //, Iterator { 26 27 const csv_max_linelength = 8000; 28 29 /** 30 * @var array array with field mapping in form column number => new_field_name 31 */ 32 public $mapping = array(); 33 34 /** 35 * @var array with conversions to be done in form: new_field_name => conversion_string 36 */ 37 public $conversion = array(); 38 39 /** 40 * @var class with extra conversion functions 41 */ 42 public $conversion_class = null; 43 44 /** 45 * @var array holding the current record 46 */ 47 protected $record = array(); 48 49 /** 50 * @var int current position counter 51 */ 52 protected $current_position = 0; 53 54 /** 55 * @var int holds total number of records 56 */ 57 protected $num_of_records = 0; 58 59 /** 60 * Cache for automatic conversion from human friendly 61 */ 62 protected static $cf_parse_cache = array(); 63 64 /** 65 * @var stream 66 */ 67 private $resource; 68 69 /** 70 * fieldseperator for csv file 71 * @access private 72 * @var char 73 */ 74 private $csv_fieldsep; 75 76 /** 77 * 78 * @var string charset of csv file 79 */ 80 private $csv_charset; 81 82 /** 83 * @param string $_resource resource containing data. May be each valid php-stream 84 * @param array $_options options for the resource array with keys: charset and fieldsep 85 */ 86 public function __construct( $_resource, array $_options ) { 87 $this->resource = $_resource; 88 $this->csv_fieldsep = $_options['fieldsep']; 89 if($_options['charset'] == 'user') $_options['charset'] = $GLOBALS['egw_info']['user']['preferences']['common']['csv_charset']; 90 $this->csv_charset = $_options['charset']; 91 return; 92 } // end of member function __construct 93 94 /** 95 * cleanup 96 */ 97 public function __destruct( ) { 98 } // end of member function __destruct 99 100 /** 101 * Returns array with the record found at position and updates the position 102 * 103 * @param mixed _position may be: {current|first|last|next|previous|somenumber} 104 * @return mixed array with data / false if no furtor records 105 */ 106 public function get_record( $_position = 'next' ) { 107 108 if ($this->get_raw_record( $_position ) === false) { 109 return false; 110 } 111 112 // skip empty records 113 if( count( array_unique( $this->record ) ) < 2 ) return $this->get_record( $_position ); 114 115 if ( !empty( $this->conversion ) ) { 116 $this->do_conversions(); 117 } 118 119 if ( !empty( $this->mapping ) ) { 120 $this->do_fieldmapping(); 121 } 122 123 return $this->record; 124 } // end of member function get_record 125 126 /** 127 * Skips $_numToSkip of records from current position on 128 * 129 * @param int $_numToSkip 130 */ 131 public function skip_records( $_numToSkip ) { 132 while ( (int)$_numToSkip-- !== 0 ) { 133 fgetcsv( $this->resource, self::csv_max_linelength, $this->csv_fieldsep); 134 } 135 } 136 137 /** 138 * updates $this->record 139 * 140 * @param mixed $_position 141 * @return bool 142 */ 143 private function get_raw_record( $_position = 'next' ) { 144 switch ($_position) { 145 case 'current' : 146 if ($this->current_position == 0) { 147 return; 148 } 149 break; 150 case 'first' : 151 if (!$this->current_position == 0) { 152 $this->current_position = 0; 153 rewind($this->resource); 154 } 155 156 case 'next' : 157 $csv_data = fgetcsv( $this->resource, self::csv_max_linelength, $this->csv_fieldsep); 158 if (!is_array($csv_data)) { 159 return false; 160 } 161 $this->current_position++; 162 $this->record = Api\Translation::convert($csv_data, $this->csv_charset); 163 break; 164 165 case 'previous' : 166 if ($this->current_position < 2) { 167 throw new Exception('Error: There is no previous record!'); 168 } 169 $final_position = --$this->current_position; 170 $this->current_position = 0; 171 rewind($this->resource); 172 while ($this->current_position !== $final_position) { 173 $this->get_raw_record(); 174 } 175 break; 176 177 case 'last' : 178 while ($this->get_raw_record()) {} 179 break; 180 181 default: //somenumber 182 if (!is_int($_position)) { 183 throw new Exception('Error: $position must be one of {current|first|last|next|previous} or an integer value'); 184 } 185 if ($_position == $this->current_position) { 186 break; 187 } 188 elseif ($_position < $this->current_position) { 189 $this->current_position = 0; 190 rewind($this->resource); 191 } 192 while ($this->current_position !== $_position) { 193 $this->get_raw_record(); 194 } 195 break; 196 } 197 return; 198 } // end of member function get_raw_record 199 200 /** 201 * Retruns total number of records for the open resource. 202 * 203 * @return int 204 */ 205 public function get_num_of_records( ) { 206 if ($this->num_of_records > 0) { 207 return $this->num_of_records; 208 } 209 $current_position = $this->current_position; 210 while ($this->get_raw_record()) {} 211 $this->num_of_records = $this->current_position; 212 $this->get_record($current_position); 213 return $this->num_of_records; 214 } // end of member function get_num_of_records 215 216 /** 217 * Returns pointer of current position 218 * 219 * @return int 220 */ 221 public function get_current_position( ) { 222 223 return $this->current_position; 224 225 } // end of member function get_current_position 226 227 228 /** 229 * does fieldmapping according to $this->mapping 230 * 231 * @return 232 */ 233 protected function do_fieldmapping( ) { 234 $record = $this->record; 235 $this->record = array(); 236 foreach ($this->mapping as $cvs_idx => $new_idx) 237 { 238 if( $new_idx == '' ) continue; 239 $this->record[$new_idx] = $record[$cvs_idx]; 240 } 241 return true; 242 } // end of member function do_fieldmapping 243 244 /** 245 * does conversions according to $this->conversion 246 * 247 * @return bool 248 */ 249 protected function do_conversions() { 250 if ( $record = importexport_helper_functions::conversion( $this->record, $this->conversion, $this->conversion_class )) { 251 $this->record = $record; 252 return; 253 } 254 throw new Exception('Error: Could not applay conversions to record'); 255 } // end of member function do_conversions 256 257 /** 258 * Automatic conversions from human values 259 * 260 * @param $fields Array of field type -> field name mappings 261 * @param $appname Appname for custom field parsing 262 * @param $selects Array of select values to be automatically parsed 263 * @param $format int 0 if records are supposed to be in DB format, 1 to treat as human values (Used for dates and select-cat) 264 * 265 * @return string warnings, if any 266 */ 267 public static function convert(Array &$record, Array $fields = array(), $appname = null, Array $selects = array(), $format=0) { 268 $warnings = array(); 269 270 // Automatic conversions 271 if($appname) { 272 273 // Load translations 274 Api\Translation::add_app($appname); 275 276 if(!self::$cf_parse_cache[$appname]) { 277 $c_fields = importexport_export_csv::convert_parse_custom_fields($record, $appname, $selects, $links, $methods); 278 self::$cf_parse_cache[$appname] = array($c_fields, $selects, $links, $methods); 279 } 280 list($c_fields, $c_selects, $links, $methods) = self::$cf_parse_cache[$appname]; 281 282 // Add in any fields that are keys to another app 283 foreach((array)$fields['links'] as $link_field => $app) 284 { 285 if(is_numeric($link_field)) continue; 286 $links[$link_field] = $app; 287 // Set it as a normal link field 288 $fields['links'][] = $link_field; 289 unset($fields['links'][$link_field]); 290 } 291 292 // Not quite a recursive merge, since only one level 293 foreach($fields as $type => &$list) 294 { 295 if($c_fields[$type]) { 296 $list = array_merge($c_fields[$type], $list); 297 unset($c_fields[$type]); 298 } 299 } 300 $fields += $c_fields; 301 $selects += $c_selects; 302 } 303 if($fields) { 304 foreach((array)$fields['select'] as $name) { 305 $record[$name] = static::find_select_key($record[$name], $selects[$name]); 306 } 307 308 foreach((array)$fields['links'] as $name) { 309 if($record[$name] && $links[$name]) 310 { 311 // Primary key to another app, not a link 312 // Text - search for a matching record 313 if(!is_numeric($record[$name])) 314 { 315 $results = Link::query($links[$name], $record[$name]); 316 if(count($results) >= 1) 317 { 318 // More than 1 result. Check for exact match 319 $exact_count = 0; 320 foreach($results as $id => $title) 321 { 322 if($title == $record[$name]) 323 { 324 $exact_count++; 325 $app_id = $id; 326 continue; 327 } 328 unset($results[$id]); 329 } 330 // Too many exact matches, or none good enough 331 if($exact_count > 1 || count($results) == 0) 332 { 333 $warnings[] = lang('Unable to link to %1 "%2"', 334 lang($links[$name]), $record[$name]). 335 ' - ' .lang('too many matches'); 336 continue; 337 } 338 elseif ($exact_count == 1) 339 { 340 $record[$name] = $app_id; 341 continue; 342 } 343 } 344 if (count($results) == 0) 345 { 346 $warnings[] = lang('Unable to link to %1 "%2"', 347 lang($links[$name]), $record[$name]). 348 ' - ' . lang('no matches'); 349 continue; 350 } else { 351 $record[$name] = key($results); 352 } 353 } 354 } 355 } 356 foreach((array)$fields['select-account'] as $name) { 357 // Compare against null to deal with empty arrays 358 if ($record[$name]) { 359 // Automatically handle text owner without explicit translation 360 $new_owner = importexport_helper_functions::account_name2id($record[$name]); 361 if(count($new_owner) != count(explode(',',$record[$name]))) 362 { 363 // Unable to parse value into account 364 $warnings[] = $name . ': ' .lang('%1 is not a known user or group', $record[$name]); 365 } 366 if($new_owner != '') { 367 $record[$name] = $new_owner; 368 } else { 369 // Clear it to prevent trouble later on 370 $record[$name] = ''; 371 } 372 } 373 } 374 foreach((array)$fields['select-bool'] as $name) { 375 if($record[$name] != null && $record[$name] != '') { 376 $record[$name] = (strtolower($record[$name]) == strtolower(lang('Yes')) || $record[$name] == '1' ? 1 : 0); 377 } 378 } 379 foreach((array)$fields['date-time'] as $name) { 380 if (isset($record[$name]) && !is_numeric($record[$name]) && strlen(trim($record[$name])) > 0) 381 { 382 // Need to handle format first 383 if($format == 1) 384 { 385 $formatted = Api\DateTime::createFromFormat( 386 '!'.Api\DateTime::$user_dateformat . ' ' .Api\DateTime::$user_timeformat, 387 $record[$name], 388 Api\DateTime::$user_timezone 389 ); 390 391 if(!$formatted && $errors = Api\DateTime::getLastErrors()) 392 { 393 // Try again, without time 394 $formatted = Api\DateTime::createFromFormat( 395 '!'.Api\DateTime::$user_dateformat, 396 trim($record[$name]), 397 Api\DateTime::$user_timezone 398 ); 399 400 if(!$formatted && $errors = Api\DateTime::getLastErrors()) 401 { 402 // Try again, anything goes 403 try { 404 $formatted = new Api\DateTime($record[$name]); 405 } catch (Exception $e) { 406 $warnings[] = $name.': ' . $e->getMessage() . "\n" . 407 'Format: '.'!'.Api\DateTime::$user_dateformat . ' ' .Api\DateTime::$user_timeformat; 408 continue; 409 } 410 $errors = Api\DateTime::getLastErrors(); 411 foreach($errors['errors'] as $char => $msg) 412 { 413 $warnings[] = "$name: [$char] $msg\n". 414 'Format: '.'!'.Api\DateTime::$user_dateformat . ' ' .Api\DateTime::$user_timeformat; 415 } 416 } 417 } 418 if($formatted) 419 { 420 $record[$name] = $formatted->getTimestamp(); 421 // Timestamp is apparently in server time, but apps will do the same conversion 422 $record[$name] = Api\DateTime::server2user($record[$name],'ts'); 423 } 424 } 425 426 if(is_array(self::$cf_parse_cache[$appname][0]['date-time']) && 427 in_array($name, self::$cf_parse_cache[$appname][0]['date-time'])) { 428 // Custom fields stored in a particular format (from customfields_widget) 429 $date_format = 'Y-m-d H:i:s'; 430 431 // Check for custom format 432 $cfs = Api\Storage\Customfields::get($appname); 433 if($cfs && $cfs[substr($name,1)] && $cfs[substr($name,1)]['values']['format']) 434 { 435 $date_format = $cfs[substr($name,1)]['values']['format']; 436 } 437 $record[$name] = date($date_format, $record[$name]); 438 } 439 } 440 if(array_key_exists($name, $record) && strlen(trim($record[$name])) == 0) 441 { 442 $record[$name] = null; 443 } 444 } 445 foreach((array)$fields['date'] as $name) { 446 if (isset($record[$name]) && !is_numeric($record[$name]) && strlen(trim($record[$name])) > 0) 447 { 448 // Need to handle format first 449 if($format == 1) 450 { 451 $formatted = Api\DateTime::createFromFormat('!'.Api\DateTime::$user_dateformat, $record[$name]); 452 if($formatted && $errors = Api\DateTime::getLastErrors() && $errors['error_count'] == 0) 453 { 454 $record[$name] = $formatted->getTimestamp(); 455 } 456 } 457 $record[$name] = Api\DateTime::server2user($record[$name],'ts'); 458 if(is_array(self::$cf_parse_cache[$appname][0]['date']) && 459 in_array($name, self::$cf_parse_cache[$appname][0]['date'])) { 460 // Custom fields stored in a particular format (from customfields_widget) 461 $date_format = 'Y-m-d'; 462 // Check for custom format 463 $cfs = Api\Storage\Customfields::get($appname); 464 if($cfs && $cfs[substr($name,1)] && $cfs[substr($name,1)]['values']['format']) 465 { 466 $date_format = $cfs[substr($name,1)]['values']['format']; 467 } 468 $record[$name] = date($date_format, $record[$name]); 469 } 470 } 471 if(array_key_exists($name, $record) && strlen(trim($record[$name])) == 0) 472 { 473 $record[$name] = null; 474 } 475 } 476 foreach((array)$fields['float'] as $name) 477 { 478 if($record[$name] != null && $record[$name] != '') { 479 $dec_point = $GLOBALS['egw_info']['user']['preferences']['common']['number_format'][0]; 480 if (empty($dec_point)) $dec_point = '.'; 481 $record[$name] = floatval(str_replace($dec_point, '.', preg_replace('/[^\d'.preg_quote($dec_point).']/', '', $record[$name]))); 482 } 483 } 484 485 // Some custom methods for conversion 486 foreach((array)$methods as $name => $method) { 487 if($record[$name]) $record[$name] = ExecMethod($method, $record[$name]); 488 } 489 490 // cat_name2id will use currentapp to create new categories 491 $current_app = $GLOBALS['egw_info']['flags']['currentapp']; 492 if($appname) { 493 $GLOBALS['egw_info']['flags']['currentapp'] = $appname; 494 } 495 $categories = new Api\Categories('',$appname); 496 foreach((array)$fields['select-cat'] as $name) { 497 if($record[$name]) { 498 // Only parse name if it needs it 499 if($format == 1) 500 { 501 $existing_cat = $categories->exists('all',$record[$name]); 502 if($existing_cat) 503 { 504 $cat_id = $existing_cat; 505 } 506 else 507 { 508 $cat_id = importexport_helper_functions::cat_name2id($record[$name]); 509 } 510 // Don't clear it if it wasn't found 511 if($cat_id) $record[$name] = $cat_id; 512 } 513 } 514 } 515 $GLOBALS['egw_info']['flags']['currentapp'] = $current_app; 516 } 517 518 return implode("\n",$warnings); 519 } 520 521 /** 522 * Find the key to match the label for a select box field, translating 523 * what we imported into a key we can save. 524 * 525 * @param String $record_value 526 * @param Array $selects Select box options in key => value pairs 527 * @return Select box key(s) that match the given record value, or the unchanged value 528 * if no matches found. 529 */ 530 protected static function find_select_key($record_value, $selects) 531 { 532 if($record_value != null && is_array($selects)) { 533 if(is_array($record_value) || is_string($record_value) && strpos($record_value, ',') !== FALSE) 534 { 535 // Array, or CSV 536 $key = array(); 537 $subs = explode(',',$record_value); 538 for($sub_index = 0; $sub_index < count($subs); $sub_index++) 539 { 540 $sub_key = static::find_select_key(trim($subs[$sub_index]), $selects); 541 if(!$sub_key) 542 { 543 $sub_key = static::find_select_key(trim($subs[$sub_index]).','.trim($subs[$sub_index+1]), $selects); 544 if($sub_key) $sub_index++; 545 } 546 if($sub_key) 547 { 548 $key[] = $sub_key; 549 } 550 } 551 return $key; 552 } 553 $key = array_search(strtolower($record_value), array_map('strtolower',$selects)); 554 if($key !== false) 555 { 556 $record_value = $key; 557 } 558 else 559 { 560 $key = array_search(strtolower($record_value), array_map('strtolower',array_map('lang',$selects))); 561 if($key !== false) $record_value = $key; 562 } 563 } 564 565 return $record_value; 566 } 567} // end of import_csv 568?> 569