3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
18 use Civi\Api4\UserJob
;
21 * This class defines the DataSource interface but must be subclassed to be
24 abstract class CRM_Import_DataSource
{
29 private $queryResultObject;
39 * @return CRM_Import_DataSource
41 public function setLimit(int $limit): CRM_Import_DataSource
{
42 $this->limit
= $limit;
43 $this->queryResultObject
= NULL;
50 * @return CRM_Import_DataSource
52 public function setOffset(int $offset): CRM_Import_DataSource
{
53 $this->offset
= $offset;
54 $this->queryResultObject
= NULL;
64 * Statuses of rows to fetch.
68 private $statuses = [];
75 private $selectFields;
78 * Fields to select as aggregates.
82 private $aggregateFields;
85 * The name of the import table.
94 public function getSelectFields(): ?
array {
95 return $this->selectFields
;
99 * @param array $selectFields
101 * @return CRM_Import_DataSource
103 public function setSelectFields(array $selectFields): CRM_Import_DataSource
{
104 $this->selectFields
= $selectFields;
109 * @param array $fields
111 * @return CRM_Import_DataSource
113 public function setAggregateFields(array $fields): CRM_Import_DataSource
{
114 $this->aggregateFields
= $fields;
121 public function getAggregateFields(): ?
array {
122 return $this->aggregateFields
;
133 * @param array $statuses
137 public function setStatuses(array $statuses): self
{
138 $this->statuses
= $statuses;
139 $this->queryResultObject
= NULL;
146 * @param int|null $userJobID
148 public function __construct(int $userJobID = NULL) {
150 $this->setUserJobID($userJobID);
155 * Form fields declared for this datasource.
159 protected $submittableFields = [];
164 * This is the primary key of the civicrm_user_job table which is used to
169 protected $userJobID;
174 public function getUserJobID(): ?
int {
175 return $this->userJobID
;
181 * @param int $userJobID
183 public function setUserJobID(int $userJobID): void
{
184 $this->userJobID
= $userJobID;
190 * This is the relevant row from civicrm_user_job.
199 * API call to retrieve the userJob row.
203 * @throws \API_Exception
205 protected function getUserJob(): array {
206 if (!$this->userJob
) {
207 $this->userJob
= UserJob
::get()
208 ->addWhere('id', '=', $this->getUserJobID())
212 return $this->userJob
;
216 * Get submitted value.
218 * Get a value submitted on the form.
222 * @throws \API_Exception
224 protected function getSubmittedValue(string $valueName) {
225 return $this->getUserJob()['metadata']['submitted_values'][$valueName];
229 * Get rows as an array.
231 * The array has all values.
233 * @param bool $nonAssociative
234 * Return as a non-associative array?
238 * @throws \API_Exception
239 * @throws \CRM_Core_Exception
241 public function getRows(bool $nonAssociative = TRUE): array {
243 while ($this->getRow()) {
244 // Historically we expect a non-associative array...
245 $rows[] = $nonAssociative ?
array_values($this->row
) : $this->row
;
247 $this->queryResultObject
= NULL;
255 * @throws \API_Exception
256 * @throws \CRM_Core_Exception
258 public function getRow(): ?
array {
259 if (!$this->queryResultObject
) {
260 $this->instantiateQueryObject();
262 if (!$this->queryResultObject
->fetch()) {
265 $values = $this->queryResultObject
->toArray();
266 /* trim whitespace around the values */
267 foreach ($values as $k => $v) {
268 $values[$k] = trim($v, " \t\r\n");
270 $this->row
= $values;
277 * The array has all values.
281 * @throws \API_Exception
282 * @throws \CRM_Core_Exception
284 public function getRowCount(array $statuses = []): int {
285 $this->statuses
= $statuses;
286 $query = 'SELECT count(*) FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
287 return CRM_Core_DAO
::singleValueQuery($query);
291 * Get an array of column headers, if any.
293 * Null is returned when there are none - ie because a csv file does not
294 * have an initial header row.
296 * This is presented to the user in the MapField screen so
297 * that can see what fields they are mapping.
300 * @throws \API_Exception
302 public function getColumnHeaders(): array {
303 return $this->getUserJob()['metadata']['DataSource']['column_headers'];
307 * Get the field names of the fields holding data in the import tracking table.
310 * @throws \API_Exception
311 * @throws \CRM_Core_Exception
313 public function getDataFieldNames(): array {
314 $result = CRM_Core_DAO
::executeQuery(
315 'SHOW FIELDS FROM ' . $this->getTableName() . "
316 WHERE Field NOT LIKE '\_%'");
318 while ($result->fetch()) {
319 $fields[] = $result->Field
;
325 * Get an array of column headers, if any.
327 * Null is returned when there are none - ie because a csv file does not
328 * have an initial header row.
330 * This is presented to the user in the MapField screen so
331 * that can see what fields they are mapping.
334 * @throws \API_Exception
336 public function getNumberOfColumns(): int {
337 return $this->getUserJob()['metadata']['DataSource']['number_of_columns'];
341 * Generated metadata relating to the the datasource.
343 * This is values that are computed within the DataSource class and
344 * which are stored in the userJob metadata in the DataSource key - eg.
346 * ['table_name' => $]
348 * Will be in the user_job.metadata field encoded into the json like
350 * `{'DataSource' : ['table_name' => $], 'submitted_values' : .....}`
354 protected $dataSourceMetadata = [];
357 * Get metadata about the datasource.
361 * @throws \API_Exception
363 public function getDataSourceMetadata(): array {
364 if (!$this->dataSourceMetadata
&& $this->getUserJobID()) {
365 $this->dataSourceMetadata
= $this->getUserJob()['metadata']['DataSource'];
368 return $this->dataSourceMetadata
;
372 * Get the table name for the datajob.
374 * @return string|null
376 * @throws \API_Exception
377 * @throws \CRM_Core_Exception
379 protected function getTableName(): ?
string {
380 // The old name is still stored...
381 $tableName = $this->getDataSourceMetadata()['table_name'];
385 if (!$this->tableName
) {
386 // If we are just loading this table we will do some validation.
387 // In the case of viewing historical jobs the table could have
388 // been deleted so we check that when we first load it.
389 if (strpos($tableName, 'civicrm_tmp_') !== 0
390 ||
!CRM_Utils_Rule
::alphanumeric($tableName)) {
391 // The table name is generated and stored by code, not users so it
392 // should be safe - but a check seems prudent all the same.
393 throw new CRM_Core_Exception('Table cannot be deleted');
395 if (!CRM_Core_DAO
::singleValueQuery('SHOW TABLES LIKE %1', [1 => [$tableName, 'String']])) {
396 throw new CRM_Import_Exception_ImportTableUnavailable('table deleted');
398 $this->tableName
= $tableName;
400 return $this->tableName
;
404 * Get the fields declared for this datasource.
408 public function getSubmittableFields(): array {
409 return $this->submittableFields
;
413 * Provides information about the data source.
416 * Description of this data source, including:
417 * - title: string, translated, required
418 * - permissions: array, optional
421 abstract public function getInfo();
424 * This is function is called by the form object to get the DataSource's form snippet.
426 * It should add all fields necessary to get the data uploaded to the temporary table in the DB.
428 * @param CRM_Core_Form $form
430 abstract public function buildQuickForm(&$form);
433 * Initialize the datasource, based on the submitted values stored in the user job.
435 * @throws \API_Exception
436 * @throws \CRM_Core_Exception
438 public function initialize(): void
{
443 * Determine if the current user has access to this data source.
447 public function checkPermission() {
448 $info = $this->getInfo();
449 return empty($info['permissions']) || CRM_Core_Permission
::check($info['permissions']);
456 * @throws \API_Exception
457 * @throws \Civi\API\Exception\UnauthorizedException
459 protected function updateUserJobMetadata(string $key, array $data): void
{
460 $metaData = array_merge(
461 $this->getUserJob()['metadata'],
464 UserJob
::update(FALSE)
465 ->addWhere('id', '=', $this->getUserJobID())
466 ->setValues(['metadata' => $metaData])
468 $this->userJob
['metadata'] = $metaData;
472 * Purge any datasource related assets when the datasource is dropped.
474 * This is the datasource's chance to delete any tables etc that it created
475 * which will now not be used.
477 * @param array $newParams
478 * If the dataSource is being updated to another variant of the same
479 * class (eg. the csv upload was set to no column headers and they
480 * have resubmitted WITH skipColumnHeader (first row is a header) then
481 * the dataSource is still CSV and the params for the new intance
482 * are passed in. When changing from csv to SQL (for example) newParams is
486 * The details to update the DataSource key in the userJob metadata to.
487 * Generally and empty array but it the datasource decided (for example)
488 * that the table it created earlier is still consistent with the new params
489 * then it might decided not to drop the table and would want to retain
492 * @throws \API_Exception
493 * @throws \CRM_Core_Exception
495 * @noinspection PhpUnusedParameterInspection
497 public function purge(array $newParams = []) :array {
498 // The old name is still stored...
499 $oldTableName = $this->getTableName();
501 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName);
507 * Add a status columns to the import table.
515 * 1) the use of the preceding underscore has 2 purposes - it avoids clashing
516 * with an id field (code comments from 14 years ago suggest perhaps there
517 * could be cases where it still clashes but time didn't tell in this case)
518 * 2) the show fields query used to get the column names excluded the
519 * administrative fields, relying on this convention.
520 * 3) we have the capitalisation on _statusMsg - @param string $tableName
522 * @throws \API_Exception
523 * @todo change to _status_message
525 protected function addTrackingFieldsToTable(string $tableName): void
{
526 CRM_Core_DAO
::executeQuery("
527 ALTER TABLE $tableName
528 ADD COLUMN _entity_id INT,
529 " . $this->getAdditionalTrackingFields() . "
530 ADD COLUMN _status VARCHAR(32) DEFAULT 'NEW' NOT NULL,
531 ADD COLUMN _status_message LONGTEXT,
532 ADD COLUMN _id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
540 * Get any additional import specific tracking fields.
542 * @throws \API_Exception
544 private function getAdditionalTrackingFields(): string {
546 $fields = $this->getParser()->getTrackingFields();
547 foreach ($fields as $fieldName => $spec) {
548 $sql .= 'ADD COLUMN _' . $fieldName . ' ' . $spec['type'] . ',';
554 * Get the import parser.
556 * @return CRM_Import_Parser
558 * @throws \API_Exception
560 private function getParser() {
562 foreach (CRM_Core_BAO_UserJob
::getTypes() as $type) {
563 if ($this->getUserJob()['job_type'] === $type['id']) {
564 $parserClass = $type['class'];
568 /* @var \CRM_Import_Parser */
569 $parser = new $parserClass();
570 $parser->setUserJobID($this->getUserJobID());
575 * Has the import job completed.
578 * True if no rows remain to be imported.
580 * @throws \API_Exception
581 * @throws \CRM_Core_Exception
583 public function isCompleted(): bool {
584 return (bool) $this->getRowCount(['new']);
588 * Update the status of the import row to reflect the processing outcome.
591 * @param string $status
592 * @param string $message
593 * @param int|null $entityID
594 * Optional created entity ID
595 * @param array $additionalFields
596 * Optional array e.g ['related_contact' => 4]
598 * @throws \API_Exception
599 * @throws \CRM_Core_Exception
601 public function updateStatus(int $id, string $status, string $message, ?
int $entityID = NULL, array $additionalFields = []): void
{
602 $sql = 'UPDATE ' . $this->getTableName() . ' SET _status = %1, _status_message = %2 ';
603 $params = [1 => [$status, 'String'], 2 => [$message, 'String']];
605 $sql .= ', _entity_id = %3';
606 $params[3] = [$entityID, 'Integer'];
609 foreach ($additionalFields as $fieldName => $value) {
610 $sql .= ', _' . $fieldName . ' = %' . $nextParam;
611 $params[$nextParam] = is_numeric($value) ?
[$value, 'Int'] : [json_encode($value), 'String'];
614 CRM_Core_DAO
::executeQuery($sql . ' WHERE _id = ' . $id, $params);
619 * @throws \API_Exception
620 * @throws \CRM_Core_Exception
622 private function instantiateQueryObject(): void
{
623 $query = 'SELECT ' . $this->getSelectClause() . ' FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
625 $query .= ' LIMIT ' . $this->limit
. ($this->offset ?
(' OFFSET ' . $this->offset
) : NULL);
627 $this->queryResultObject
= CRM_Core_DAO
::executeQuery($query);
633 private function getSelectClause(): string {
634 if ($this->getAggregateFields()) {
636 foreach ($this->getAggregateFields() as $field) {
637 $fields[] = $field['operation'] . '(_' . $field['name'] . ') as ' . $field['name'];
639 return implode(',', $fields);
641 return $this->getSelectFields() ?
'`' . implode('`, `', $this->getSelectFields()) . '`' : '*';
645 * Get the mapping of constants to database status codes.
649 protected function getStatusMapping(): array {
651 CRM_Import_Parser
::VALID
=> ['imported', 'new', 'soft_credit_imported', 'pledge_payment_imported'],
652 CRM_Import_Parser
::ERROR
=> ['error', 'invalid', 'soft_credit_error', 'pledge_payment_error'],
653 CRM_Import_Parser
::DUPLICATE
=> ['duplicate'],
654 CRM_Import_Parser
::NO_MATCH
=> ['invalid_no_match'],
655 CRM_Import_Parser
::UNPARSED_ADDRESS_WARNING
=> ['warning_unparsed_address'],
656 CRM_Contribute_Import_Parser_Contribution
::SOFT_CREDIT_ERROR
=> ['soft_credit_error'],
657 CRM_Contribute_Import_Parser_Contribution
::SOFT_CREDIT
=> ['soft_credit_imported'],
658 CRM_Contribute_Import_Parser_Contribution
::PLEDGE_PAYMENT
=> ['pledge_payment_imported'],
659 CRM_Contribute_Import_Parser_Contribution
::PLEDGE_PAYMENT_ERROR
=> ['pledge_payment_error'],
665 * Get the status filter clause.
669 private function getStatusClause(): string {
670 if (!empty($this->statuses
)) {
672 foreach ($this->statuses
as $status) {
673 foreach ($this->getStatusMapping()[$status] as $statusName) {
674 $statuses[] = '"' . $statusName . '"';
677 return ' WHERE _status IN (' . implode(',', $statuses) . ')';