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