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    * Determine if the current user has access to this data source.
 352    *
 353    * @return bool
 354    */
 355   public function checkPermission() {
 356     $info = $this->getInfo();
 357     return empty($info['permissions']) || CRM_Core_Permission::check($info['permissions']);
 358   }
 359
 360   /**
 361    * @param string $key
 362    * @param array $data
 363    *
 364    * @throws \API_Exception
 365    * @throws \Civi\API\Exception\UnauthorizedException
 366    */
 367   protected function updateUserJobMetadata(string $key, array $data): void {
 368     $metaData = array_merge(
 369       $this->getUserJob()['metadata'],
 370       [$key => $data]
 371     );
 372     UserJob::update(FALSE)
 373       ->addWhere('id', '=', $this->getUserJobID())
 374       ->setValues(['metadata' => $metaData])
 375       ->execute();
 376     $this->userJob['metadata'] = $metaData;
 377   }
 378
 379   /**
 380    * Purge any datasource related assets when the datasource is dropped.
 381    *
 382    * This is the datasource's chance to delete any tables etc that it created
 383    * which will now not be used.
 384    *
 385    * @param array $newParams
 386    *   If the dataSource is being updated to another variant of the same
 387    *   class (eg. the csv upload was set to no column headers and they
 388    *   have resubmitted WITH skipColumnHeader (first row is a header) then
 389    *   the dataSource is still CSV and the params for the new intance
 390    *   are passed in. When changing from csv to SQL (for example) newParams is
 391    *   empty.
 392    *
 393    * @return array
 394    *   The details to update the DataSource key in the userJob metadata to.
 395    *   Generally and empty array but it the datasource decided (for example)
 396    *   that the table it created earlier is still consistent with the new params
 397    *   then it might decided not to drop the table and would want to retain
 398    *   some metadata.
 399    *
 400    * @throws \API_Exception
 401    * @throws \CRM_Core_Exception
 402    *
 403    * @noinspection PhpUnusedParameterInspection
 404    */
 405   public function purge(array $newParams = []) :array {
 406     // The old name is still stored...
 407     $oldTableName = $this->getTableName();
 408     if ($oldTableName) {
 409       CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName);
 410     }
 411     return [];
 412   }
 413
 414   /**
 415    * Add a status columns to the import table.
 416    *
 417    * We add
 418    *  _id - primary key
 419    *  _status
 420    *  _statusMsg
 421    *
 422    * Note that
 423    * 1) the use of the preceding underscore has 2 purposes - it avoids clashing
 424    *   with an id field (code comments from 14 years ago suggest perhaps there
 425    *   could be cases where it still clashes but time didn't tell in this case)
 426    * 2) the show fields query used to get the column names excluded the
 427    *   administrative fields, relying on this convention.
 428    * 3) we have the capitalisation on _statusMsg - @todo change to _status_message
 429    *
 430    * @param string $tableName
 431    */
 432   protected function addTrackingFieldsToTable(string $tableName): void {
 433     CRM_Core_DAO::executeQuery("
 434      ALTER TABLE $tableName
 435        ADD COLUMN _status VARCHAR(32) DEFAULT 'NEW' NOT NULL,
 436        ADD COLUMN _statusMsg TEXT,
 437        ADD COLUMN _id INT PRIMARY KEY NOT NULL AUTO_INCREMENT"
 438     );
 439   }
 440
 441   /**
 442    *
 443    * @throws \API_Exception
 444    * @throws \CRM_Core_Exception
 445    */
 446   private function instantiateQueryObject(): void {
 447     $query = 'SELECT * FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
 448     if ($this->limit) {
 449       $query .= ' LIMIT ' . $this->limit . ($this->offset ? (' OFFSET ' . $this->offset) : NULL);
 450     }
 451     $this->queryResultObject = CRM_Core_DAO::executeQuery($query);
 452   }
 453
 454   /**
 455    * Get the mapping of constants to database status codes.
 456    *
 457    * @return array[]
 458    */
 459   protected function getStatusMapping(): array {
 460     return [
 461       CRM_Import_Parser::VALID => ['imported', 'new'],
 462       CRM_Import_Parser::ERROR => ['error'],
 463       CRM_Import_Parser::DUPLICATE => ['duplicate'],
 464     ];
 465   }
 466
 467   /**
 468    * Get the status filter clause.
 469    *
 470    * @return string
 471    */
 472   private function getStatusClause(): string {
 473     if (!empty($this->statuses)) {
 474       $statuses = [];
 475       foreach ($this->statuses as $status) {
 476         foreach ($this->getStatusMapping()[$status] as $statusName) {
 477           $statuses[] = '"' . $statusName . '"';
 478         }
 479       }
 480       return ' WHERE _status IN (' . implode(',', $statuses) . ')';
 481     }
 482     return '';
 483   }
 484
 485 }