CRM/Import/DataSource.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17
  18 use Civi\Api4\UserJob;
  19
  20 /**
  21  * This class defines the DataSource interface but must be subclassed to be
  22  * useful.
  23  */
  24 abstract class CRM_Import_DataSource {
  25
  26   /**
  27    * @var \CRM_Core_DAO
  28    */
  29   private $queryResultObject;
  30
  31   /**
  32    * @var int
  33    */
  34   private $limit;
  35
  36   /**
  37    * @param int $limit
  38    *
  39    * @return CRM_Import_DataSource
  40    */
  41   public function setLimit(int $limit): CRM_Import_DataSource {
  42     $this->limit = $limit;
  43     $this->queryResultObject = NULL;
  44     return $this;
  45   }
  46
  47   /**
  48    * @param int $offset
  49    *
  50    * @return CRM_Import_DataSource
  51    */
  52   public function setOffset(int $offset): CRM_Import_DataSource {
  53     $this->offset = $offset;
  54     $this->queryResultObject = NULL;
  55     return $this;
  56   }
  57
  58   /**
  59    * @var int
  60    */
  61   private $offset;
  62
  63   /**
  64    * Statuses of rows to fetch.
  65    *
  66    * @var array
  67    */
  68   private $statuses = [];
  69
  70   /**
  71    * Fields to select.
  72    *
  73    * @var array
  74    */
  75   private $selectFields;
  76
  77   /**
  78    * @return array|null
  79    */
  80   public function getSelectFields(): ?array {
  81     return $this->selectFields;
  82   }
  83
  84   /**
  85    * @param array $selectFields
  86    *
  87    * @return CRM_Import_DataSource
  88    */
  89   public function setSelectFields(array $selectFields): CRM_Import_DataSource {
  90     $this->selectFields = $selectFields;
  91     return $this;
  92   }
  93
  94   /**
  95    * Current row.
  96    *
  97    * @var array
  98    */
  99   private $row;
 100
 101   /**
 102    * @param array $statuses
 103    *
 104    * @return self
 105    */
 106   public function setStatuses(array $statuses): self {
 107     $this->statuses = $statuses;
 108     $this->queryResultObject = NULL;
 109     return $this;
 110   }
 111
 112   /**
 113    * Class constructor.
 114    *
 115    * @param int|null $userJobID
 116    */
 117   public function __construct(int $userJobID = NULL) {
 118     if ($userJobID) {
 119       $this->setUserJobID($userJobID);
 120     }
 121   }
 122
 123   /**
 124    * Form fields declared for this datasource.
 125    *
 126    * @var string[]
 127    */
 128   protected $submittableFields = [];
 129
 130   /**
 131    * User job id.
 132    *
 133    * This is the primary key of the civicrm_user_job table which is used to
 134    * track the import.
 135    *
 136    * @var int
 137    */
 138   protected $userJobID;
 139
 140   /**
 141    * @return int|null
 142    */
 143   public function getUserJobID(): ?int {
 144     return $this->userJobID;
 145   }
 146
 147   /**
 148    * Set user job ID.
 149    *
 150    * @param int $userJobID
 151    */
 152   public function setUserJobID(int $userJobID): void {
 153     $this->userJobID = $userJobID;
 154   }
 155
 156   /**
 157    * User job details.
 158    *
 159    * This is the relevant row from civicrm_user_job.
 160    *
 161    * @var array
 162    */
 163   protected $userJob;
 164
 165   /**
 166    * Get User Job.
 167    *
 168    * API call to retrieve the userJob row.
 169    *
 170    * @return array
 171    *
 172    * @throws \API_Exception
 173    */
 174   protected function getUserJob(): array {
 175     if (!$this->userJob) {
 176       $this->userJob = UserJob::get()
 177         ->addWhere('id', '=', $this->getUserJobID())
 178         ->execute()
 179         ->first();
 180     }
 181     return $this->userJob;
 182   }
 183
 184   /**
 185    * Get submitted value.
 186    *
 187    * Get a value submitted on the form.
 188    *
 189    * @return mixed
 190    *
 191    * @throws \API_Exception
 192    */
 193   protected function getSubmittedValue(string $valueName) {
 194     return $this->getUserJob()['metadata']['submitted_values'][$valueName];
 195   }
 196
 197   /**
 198    * Get rows as an array.
 199    *
 200    * The array has all values.
 201    *
 202    * @param bool $nonAssociative
 203    *   Return as a non-associative array?
 204    *
 205    * @return array
 206    *
 207    * @throws \API_Exception
 208    * @throws \CRM_Core_Exception
 209    */
 210   public function getRows(bool $nonAssociative = TRUE): array {
 211     $rows = [];
 212     while ($this->getRow()) {
 213       // Historically we expect a non-associative array...
 214       $rows[] = $nonAssociative ? array_values($this->row) : $this->row;
 215     }
 216     $this->queryResultObject = NULL;
 217     return $rows;
 218   }
 219
 220   /**
 221    * Get the next row.
 222    *
 223    * @return array|null
 224    * @throws \API_Exception
 225    * @throws \CRM_Core_Exception
 226    */
 227   public function getRow(): ?array {
 228     if (!$this->queryResultObject) {
 229       $this->instantiateQueryObject();
 230     }
 231     if (!$this->queryResultObject->fetch()) {
 232       return NULL;
 233     }
 234     $values = $this->queryResultObject->toArray();
 235     /* trim whitespace around the values */
 236     foreach ($values as $k => $v) {
 237       $values[$k] = trim($v, " \t\r\n");
 238     }
 239     $this->row = $values;
 240     return $values;
 241   }
 242
 243   /**
 244    * Get row count.
 245    *
 246    * The array has all values.
 247    *
 248    * @return int
 249    *
 250    * @throws \API_Exception
 251    * @throws \CRM_Core_Exception
 252    */
 253   public function getRowCount(array $statuses = []): int {
 254     $this->statuses = $statuses;
 255     $query = 'SELECT count(*) FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
 256     return CRM_Core_DAO::singleValueQuery($query);
 257   }
 258
 259   /**
 260    * Get an array of column headers, if any.
 261    *
 262    * Null is returned when there are none - ie because a csv file does not
 263    * have an initial header row.
 264    *
 265    * This is presented to the user in the MapField screen so
 266    * that can see what fields they are mapping.
 267    *
 268    * @return array
 269    * @throws \API_Exception
 270    */
 271   public function getColumnHeaders(): array {
 272     return $this->getUserJob()['metadata']['DataSource']['column_headers'];
 273   }
 274
 275   /**
 276    * Get an array of column headers, if any.
 277    *
 278    * Null is returned when there are none - ie because a csv file does not
 279    * have an initial header row.
 280    *
 281    * This is presented to the user in the MapField screen so
 282    * that can see what fields they are mapping.
 283    *
 284    * @return int
 285    * @throws \API_Exception
 286    */
 287   public function getNumberOfColumns(): int {
 288     return $this->getUserJob()['metadata']['DataSource']['number_of_columns'];
 289   }
 290
 291   /**
 292    * Generated metadata relating to the the datasource.
 293    *
 294    * This is values that are computed within the DataSource class and
 295    * which are stored in the userJob metadata in the DataSource key - eg.
 296    *
 297    * ['table_name' => $]
 298    *
 299    * Will be in the user_job.metadata field encoded into the json like
 300    *
 301    * `{'DataSource' : ['table_name' => $], 'submitted_values' : .....}`
 302    *
 303    * @var array
 304    */
 305   protected $dataSourceMetadata = [];
 306
 307   /**
 308    * Get metadata about the datasource.
 309    *
 310    * @return array
 311    *
 312    * @throws \API_Exception
 313    */
 314   public function getDataSourceMetadata(): array {
 315     if (!$this->dataSourceMetadata && $this->getUserJobID()) {
 316       $this->dataSourceMetadata = $this->getUserJob()['metadata']['DataSource'];
 317     }
 318
 319     return $this->dataSourceMetadata;
 320   }
 321
 322   /**
 323    * Get the table name for the datajob.
 324    *
 325    * @return string|null
 326    *
 327    * @throws \API_Exception
 328    * @throws \CRM_Core_Exception
 329    */
 330   protected function getTableName(): ?string {
 331     // The old name is still stored...
 332     $tableName = $this->getDataSourceMetadata()['table_name'];
 333     if (!$tableName) {
 334       return NULL;
 335     }
 336     if (strpos($tableName, 'civicrm_tmp_') !== 0
 337       || !CRM_Utils_Rule::alphanumeric($tableName)) {
 338       // The table name is generated and stored by code, not users so it
 339       // should be safe - but a check seems prudent all the same.
 340       throw new CRM_Core_Exception('Table cannot be deleted');
 341     }
 342     return $tableName;
 343   }
 344
 345   /**
 346    * Get the fields declared for this datasource.
 347    *
 348    * @return string[]
 349    */
 350   public function getSubmittableFields(): array {
 351     return $this->submittableFields;
 352   }
 353
 354   /**
 355    * Provides information about the data source.
 356    *
 357    * @return array
 358    *   Description of this data source, including:
 359    *   - title: string, translated, required
 360    *   - permissions: array, optional
 361    *
 362    */
 363   abstract public function getInfo();
 364
 365   /**
 366    * This is function is called by the form object to get the DataSource's form snippet.
 367    *
 368    * It should add all fields necessary to get the data uploaded to the temporary table in the DB.
 369    *
 370    * @param CRM_Core_Form $form
 371    */
 372   abstract public function buildQuickForm(&$form);
 373
 374   /**
 375    * Initialize the datasource, based on the submitted values stored in the user job.
 376    *
 377    * @throws \API_Exception
 378    * @throws \CRM_Core_Exception
 379    */
 380   public function initialize(): void {
 381
 382   }
 383
 384   /**
 385    * Determine if the current user has access to this data source.
 386    *
 387    * @return bool
 388    */
 389   public function checkPermission() {
 390     $info = $this->getInfo();
 391     return empty($info['permissions']) || CRM_Core_Permission::check($info['permissions']);
 392   }
 393
 394   /**
 395    * @param string $key
 396    * @param array $data
 397    *
 398    * @throws \API_Exception
 399    * @throws \Civi\API\Exception\UnauthorizedException
 400    */
 401   protected function updateUserJobMetadata(string $key, array $data): void {
 402     $metaData = array_merge(
 403       $this->getUserJob()['metadata'],
 404       [$key => $data]
 405     );
 406     UserJob::update(FALSE)
 407       ->addWhere('id', '=', $this->getUserJobID())
 408       ->setValues(['metadata' => $metaData])
 409       ->execute();
 410     $this->userJob['metadata'] = $metaData;
 411   }
 412
 413   /**
 414    * Purge any datasource related assets when the datasource is dropped.
 415    *
 416    * This is the datasource's chance to delete any tables etc that it created
 417    * which will now not be used.
 418    *
 419    * @param array $newParams
 420    *   If the dataSource is being updated to another variant of the same
 421    *   class (eg. the csv upload was set to no column headers and they
 422    *   have resubmitted WITH skipColumnHeader (first row is a header) then
 423    *   the dataSource is still CSV and the params for the new intance
 424    *   are passed in. When changing from csv to SQL (for example) newParams is
 425    *   empty.
 426    *
 427    * @return array
 428    *   The details to update the DataSource key in the userJob metadata to.
 429    *   Generally and empty array but it the datasource decided (for example)
 430    *   that the table it created earlier is still consistent with the new params
 431    *   then it might decided not to drop the table and would want to retain
 432    *   some metadata.
 433    *
 434    * @throws \API_Exception
 435    * @throws \CRM_Core_Exception
 436    *
 437    * @noinspection PhpUnusedParameterInspection
 438    */
 439   public function purge(array $newParams = []) :array {
 440     // The old name is still stored...
 441     $oldTableName = $this->getTableName();
 442     if ($oldTableName) {
 443       CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName);
 444     }
 445     return [];
 446   }
 447
 448   /**
 449    * Add a status columns to the import table.
 450    *
 451    * We add
 452    *  _id - primary key
 453    *  _status
 454    *  _statusMsg
 455    *
 456    * Note that
 457    * 1) the use of the preceding underscore has 2 purposes - it avoids clashing
 458    *   with an id field (code comments from 14 years ago suggest perhaps there
 459    *   could be cases where it still clashes but time didn't tell in this case)
 460    * 2) the show fields query used to get the column names excluded the
 461    *   administrative fields, relying on this convention.
 462    * 3) we have the capitalisation on _statusMsg - @param string $tableName
 463    *
 464    * @throws \API_Exception
 465    * @todo change to _status_message
 466    */
 467   protected function addTrackingFieldsToTable(string $tableName): void {
 468     CRM_Core_DAO::executeQuery("
 469      ALTER TABLE $tableName
 470        ADD COLUMN _entity_id INT,
 471        " . $this->getAdditionalTrackingFields() . "
 472        ADD COLUMN _status VARCHAR(32) DEFAULT 'NEW' NOT NULL,
 473        ADD COLUMN _status_message TEXT,
 474        ADD COLUMN _id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
 475        ADD INDEX(_id),
 476        ADD INDEX(_status)
 477        "
 478     );
 479   }
 480
 481   /**
 482    * Get any additional import specific tracking fields.
 483    *
 484    * @throws \API_Exception
 485    */
 486   private function getAdditionalTrackingFields(): string {
 487     $sql = '';
 488     $fields = $this->getParser()->getTrackingFields();
 489     foreach ($fields as $fieldName => $spec) {
 490       $sql .= 'ADD COLUMN  _' . $fieldName . ' ' . $spec . ',';
 491     }
 492     return $sql;
 493   }
 494
 495   /**
 496    * Get the import parser.
 497    *
 498    * @return CRM_Import_Parser
 499    *
 500    * @throws \API_Exception
 501    */
 502   private function getParser() {
 503     $parserClass = '';
 504     foreach (CRM_Core_BAO_UserJob::getTypes() as $type) {
 505       if ($this->getUserJob()['type_id'] === $type['id']) {
 506         $parserClass = $type['class'];
 507       }
 508     }
 509     /* @var \CRM_Import_Parser */
 510     $parser = new $parserClass();
 511     $parser->setUserJobID($this->getUserJobID());
 512     return $parser;
 513   }
 514
 515   /**
 516    * Has the import job completed.
 517    *
 518    * @return bool
 519    *   True if no rows remain to be imported.
 520    *
 521    * @throws \API_Exception
 522    * @throws \CRM_Core_Exception
 523    */
 524   public function isCompleted(): bool {
 525     return (bool) $this->getRowCount(['new']);
 526   }
 527
 528   /**
 529    * Update the status of the import row to reflect the processing outcome.
 530    *
 531    * @param int $id
 532    * @param string $status
 533    * @param string $message
 534    * @param int|null $entityID
 535    *   Optional created entity ID
 536    * @param array $additionalFields
 537    *   Optional array e.g ['related_contact' => 4]
 538    *
 539    * @throws \API_Exception
 540    * @throws \CRM_Core_Exception
 541    */
 542   public function updateStatus(int $id, string $status, string $message, ? int $entityID = NULL, array $additionalFields = []): void {
 543     $sql = 'UPDATE ' . $this->getTableName() . ' SET _status = %1, _status_message = %2 ';
 544     $params = [1 => [$status, 'String'], 2 => [$message, 'String']];
 545     if ($entityID) {
 546       $sql .= ', _entity_id = %3';
 547       $params[3] = [$entityID, 'Integer'];
 548     }
 549     $nextParam = 4;
 550     foreach ($additionalFields as $fieldName => $value) {
 551       $sql .= ', _' . $fieldName . ' = %' . $nextParam;
 552       $params[$nextParam] = is_numeric($value) ? [$value, 'Int'] : [json_encode($value), 'String'];
 553       $nextParam++;
 554     }
 555     CRM_Core_DAO::executeQuery($sql . ' WHERE _id = ' . $id, $params);
 556   }
 557
 558   /**
 559    *
 560    * @throws \API_Exception
 561    * @throws \CRM_Core_Exception
 562    */
 563   private function instantiateQueryObject(): void {
 564     $query = 'SELECT ' . $this->getSelectClause() . ' FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
 565     if ($this->limit) {
 566       $query .= ' LIMIT ' . $this->limit . ($this->offset ? (' OFFSET ' . $this->offset) : NULL);
 567     }
 568     $this->queryResultObject = CRM_Core_DAO::executeQuery($query);
 569   }
 570
 571   /**
 572    * @return string
 573    */
 574   private function getSelectClause(): string {
 575     return $this->getSelectFields() ? implode(', ', $this->getSelectFields()) : '*';
 576   }
 577
 578   /**
 579    * Get the mapping of constants to database status codes.
 580    *
 581    * @return array[]
 582    */
 583   protected function getStatusMapping(): array {
 584     return [
 585       CRM_Import_Parser::VALID => ['imported', 'new', 'soft_credit_imported', 'pledge_payment_imported'],
 586       CRM_Import_Parser::ERROR => ['error', 'invalid', 'soft_credit_error', 'pledge_payment_error'],
 587       CRM_Import_Parser::DUPLICATE => ['duplicate'],
 588       CRM_Import_Parser::NO_MATCH => ['invalid_no_match'],
 589       CRM_Import_Parser::UNPARSED_ADDRESS_WARNING => ['warning_unparsed_address'],
 590       CRM_Contribute_Import_Parser_Contribution::SOFT_CREDIT_ERROR => ['soft_credit_error'],
 591       CRM_Contribute_Import_Parser_Contribution::SOFT_CREDIT => ['soft_credit_imported'],
 592       CRM_Contribute_Import_Parser_Contribution::PLEDGE_PAYMENT => ['pledge_payment_imported'],
 593       CRM_Contribute_Import_Parser_Contribution::PLEDGE_PAYMENT_ERROR => ['pledge_payment_error'],
 594       'new' => ['new'],
 595     ];
 596   }
 597
 598   /**
 599    * Get the status filter clause.
 600    *
 601    * @return string
 602    */
 603   private function getStatusClause(): string {
 604     if (!empty($this->statuses)) {
 605       $statuses = [];
 606       foreach ($this->statuses as $status) {
 607         foreach ($this->getStatusMapping()[$status] as $statusName) {
 608           $statuses[] = '"' . $statusName . '"';
 609         }
 610       }
 611       return ' WHERE _status IN (' . implode(',', $statuses) . ')';
 612     }
 613     return '';
 614   }
 615
 616 }