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 = [];
78 * @param array $statuses
82 public function setStatuses(array $statuses): self
{
83 $this->statuses
= $statuses;
84 $this->queryResultObject
= NULL;
91 * @param int|null $userJobID
93 public function __construct(int $userJobID = NULL) {
95 $this->setUserJobID($userJobID);
100 * Form fields declared for this datasource.
104 protected $submittableFields = [];
109 * This is the primary key of the civicrm_user_job table which is used to
114 protected $userJobID;
119 public function getUserJobID(): ?
int {
120 return $this->userJobID
;
126 * @param int $userJobID
128 public function setUserJobID(int $userJobID): void
{
129 $this->userJobID
= $userJobID;
135 * This is the relevant row from civicrm_user_job.
144 * API call to retrieve the userJob row.
148 * @throws \API_Exception
150 protected function getUserJob(): array {
151 if (!$this->userJob
) {
152 $this->userJob
= UserJob
::get()
153 ->addWhere('id', '=', $this->getUserJobID())
157 return $this->userJob
;
161 * Get submitted value.
163 * Get a value submitted on the form.
167 * @throws \API_Exception
169 protected function getSubmittedValue(string $valueName) {
170 return $this->getUserJob()['metadata']['submitted_values'][$valueName];
174 * Get rows as an array.
176 * The array has all values.
178 * @param bool $nonAssociative
179 * Return as a non-associative array?
183 * @throws \API_Exception
184 * @throws \CRM_Core_Exception
186 public function getRows(bool $nonAssociative = TRUE): array {
188 while ($this->getRow()) {
189 // Historically we expect a non-associative array...
190 $rows[] = $nonAssociative ?
array_values($this->row
) : $this->row
;
192 $this->queryResultObject
= NULL;
200 * @throws \API_Exception
201 * @throws \CRM_Core_Exception
203 public function getRow(): ?
array {
204 if (!$this->queryResultObject
) {
205 $this->instantiateQueryObject();
207 if (!$this->queryResultObject
->fetch()) {
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");
215 $this->row
= $values;
222 * The array has all values.
226 * @throws \API_Exception
227 * @throws \CRM_Core_Exception
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);
236 * Get an array of column headers, if any.
238 * Null is returned when there are none - ie because a csv file does not
239 * have an initial header row.
241 * This is presented to the user in the MapField screen so
242 * that can see what fields they are mapping.
245 * @throws \API_Exception
247 public function getColumnHeaders(): array {
248 return $this->getUserJob()['metadata']['DataSource']['column_headers'];
252 * Get an array of column headers, if any.
254 * Null is returned when there are none - ie because a csv file does not
255 * have an initial header row.
257 * This is presented to the user in the MapField screen so
258 * that can see what fields they are mapping.
261 * @throws \API_Exception
263 public function getNumberOfColumns(): int {
264 return $this->getUserJob()['metadata']['DataSource']['number_of_columns'];
268 * Generated metadata relating to the the datasource.
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.
273 * ['table_name' => $]
275 * Will be in the user_job.metadata field encoded into the json like
277 * `{'DataSource' : ['table_name' => $], 'submitted_values' : .....}`
281 protected $dataSourceMetadata = [];
284 * Get metadata about the datasource.
288 * @throws \API_Exception
290 public function getDataSourceMetadata(): array {
291 if (!$this->dataSourceMetadata
&& $this->getUserJobID()) {
292 $this->dataSourceMetadata
= $this->getUserJob()['metadata']['DataSource'];
295 return $this->dataSourceMetadata
;
299 * Get the table name for the datajob.
301 * @return string|null
303 * @throws \API_Exception
304 * @throws \CRM_Core_Exception
306 protected function getTableName(): ?
string {
307 // The old name is still stored...
308 $tableName = $this->getDataSourceMetadata()['table_name'];
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');
322 * Get the fields declared for this datasource.
326 public function getSubmittableFields(): array {
327 return $this->submittableFields
;
331 * Provides information about the data source.
334 * Description of this data source, including:
335 * - title: string, translated, required
336 * - permissions: array, optional
339 abstract public function getInfo();
342 * This is function is called by the form object to get the DataSource's form snippet.
344 * It should add all fields necessary to get the data uploaded to the temporary table in the DB.
346 * @param CRM_Core_Form $form
348 abstract public function buildQuickForm(&$form);
351 * Initialize the datasource, based on the submitted values stored in the user job.
353 * @throws \API_Exception
354 * @throws \CRM_Core_Exception
356 public function initialize(): void
{
361 * Determine if the current user has access to this data source.
365 public function checkPermission() {
366 $info = $this->getInfo();
367 return empty($info['permissions']) || CRM_Core_Permission
::check($info['permissions']);
374 * @throws \API_Exception
375 * @throws \Civi\API\Exception\UnauthorizedException
377 protected function updateUserJobMetadata(string $key, array $data): void
{
378 $metaData = array_merge(
379 $this->getUserJob()['metadata'],
382 UserJob
::update(FALSE)
383 ->addWhere('id', '=', $this->getUserJobID())
384 ->setValues(['metadata' => $metaData])
386 $this->userJob
['metadata'] = $metaData;
390 * Purge any datasource related assets when the datasource is dropped.
392 * This is the datasource's chance to delete any tables etc that it created
393 * which will now not be used.
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
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
410 * @throws \API_Exception
411 * @throws \CRM_Core_Exception
413 * @noinspection PhpUnusedParameterInspection
415 public function purge(array $newParams = []) :array {
416 // The old name is still stored...
417 $oldTableName = $this->getTableName();
419 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS ' . $oldTableName);
425 * Add a status columns to the import table.
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
440 * @param string $tableName
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"
454 * Has the import job completed.
457 * True if no rows remain to be imported.
459 * @throws \API_Exception
460 * @throws \CRM_Core_Exception
462 public function isCompleted(): bool {
463 return (bool) $this->getRowCount(['new']);
467 * Update the status of the import row to reflect the processing outcome.
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]
477 * @throws \API_Exception
478 * @throws \CRM_Core_Exception
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']];
484 $sql .= ', _entity_id = %3';
485 $params[3] = [$entityID, 'Integer'];
487 if ($relatedEntityIDs) {
488 $sql .= ', _related_entities = %4';
489 $params[4] = [json_encode($relatedEntityIDs), 'String'];
491 CRM_Core_DAO
::executeQuery($sql . ' WHERE _id = ' . $id, $params);
496 * @throws \API_Exception
497 * @throws \CRM_Core_Exception
499 private function instantiateQueryObject(): void
{
500 $query = 'SELECT * FROM ' . $this->getTableName() . ' ' . $this->getStatusClause();
502 $query .= ' LIMIT ' . $this->limit
. ($this->offset ?
(' OFFSET ' . $this->offset
) : NULL);
504 $this->queryResultObject
= CRM_Core_DAO
::executeQuery($query);
508 * Get the mapping of constants to database status codes.
512 protected function getStatusMapping(): array {
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'],
524 * Get the status filter clause.
528 private function getStatusClause(): string {
529 if (!empty($this->statuses
)) {
531 foreach ($this->statuses
as $status) {
532 foreach ($this->getStatusMapping()[$status] as $statusName) {
533 $statuses[] = '"' . $statusName . '"';
536 return ' WHERE _status IN (' . implode(',', $statuses) . ')';