| 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 _status VARCHAR(32) DEFAULT 'NEW' NOT NULL, |
| 446 | ADD COLUMN _statusMsg TEXT, |
| 447 | ADD COLUMN _id INT PRIMARY KEY NOT NULL AUTO_INCREMENT" |
| 448 | ); |
| 449 | } |
| 450 | |
| 451 | /** |
| 452 | * |
| 453 | * @throws \API_Exception |
| 454 | * @throws \CRM_Core_Exception |
| 455 | */ |
| 456 | private function instantiateQueryObject(): void { |
| 457 | $query = 'SELECT * FROM ' . $this->getTableName() . ' ' . $this->getStatusClause(); |
| 458 | if ($this->limit) { |
| 459 | $query .= ' LIMIT ' . $this->limit . ($this->offset ? (' OFFSET ' . $this->offset) : NULL); |
| 460 | } |
| 461 | $this->queryResultObject = CRM_Core_DAO::executeQuery($query); |
| 462 | } |
| 463 | |
| 464 | /** |
| 465 | * Get the mapping of constants to database status codes. |
| 466 | * |
| 467 | * @return array[] |
| 468 | */ |
| 469 | protected function getStatusMapping(): array { |
| 470 | return [ |
| 471 | CRM_Import_Parser::VALID => ['imported', 'new'], |
| 472 | CRM_Import_Parser::ERROR => ['error', 'invalid'], |
| 473 | CRM_Import_Parser::DUPLICATE => ['duplicate'], |
| 474 | CRM_Import_Parser::NO_MATCH => ['invalid_no_match'], |
| 475 | CRM_Import_Parser::UNPARSED_ADDRESS_WARNING => ['warning_unparsed_address'], |
| 476 | ]; |
| 477 | } |
| 478 | |
| 479 | /** |
| 480 | * Get the status filter clause. |
| 481 | * |
| 482 | * @return string |
| 483 | */ |
| 484 | private function getStatusClause(): string { |
| 485 | if (!empty($this->statuses)) { |
| 486 | $statuses = []; |
| 487 | foreach ($this->statuses as $status) { |
| 488 | foreach ($this->getStatusMapping()[$status] as $statusName) { |
| 489 | $statuses[] = '"' . $statusName . '"'; |
| 490 | } |
| 491 | } |
| 492 | return ' WHERE _status IN (' . implode(',', $statuses) . ')'; |
| 493 | } |
| 494 | return ''; |
| 495 | } |
| 496 | |
| 497 | } |