CRM/Import/Forms.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 use League\Csv\Writer;
  20
  21 /**
  22  * This class helps the forms within the import flow access submitted & parsed values.
  23  */
  24 class CRM_Import_Forms extends CRM_Core_Form {
  25
  26   /**
  27    * User job id.
  28    *
  29    * This is the primary key of the civicrm_user_job table which is used to
  30    * track the import.
  31    *
  32    * @var int
  33    */
  34   protected $userJobID;
  35
  36   /**
  37    * @return int|null
  38    */
  39   public function getUserJobID(): ?int {
  40     if (!$this->userJobID && $this->get('user_job_id')) {
  41       $this->userJobID = $this->get('user_job_id');
  42     }
  43     return $this->userJobID;
  44   }
  45
  46   /**
  47    * Set user job ID.
  48    *
  49    * @param int $userJobID
  50    */
  51   public function setUserJobID(int $userJobID): void {
  52     $this->userJobID = $userJobID;
  53     // This set allows other forms in the flow ot use $this->get('user_job_id').
  54     $this->set('user_job_id', $userJobID);
  55   }
  56
  57   /**
  58    * User job details.
  59    *
  60    * This is the relevant row from civicrm_user_job.
  61    *
  62    * @var array
  63    */
  64   protected $userJob;
  65
  66   /**
  67    * @var \CRM_Import_Parser
  68    */
  69   protected $parser;
  70
  71   /**
  72    * Get User Job.
  73    *
  74    * API call to retrieve the userJob row.
  75    *
  76    * @return array
  77    *
  78    * @throws \API_Exception
  79    */
  80   protected function getUserJob(): array {
  81     if (!$this->userJob) {
  82       $this->userJob = UserJob::get()
  83         ->addWhere('id', '=', $this->getUserJobID())
  84         ->execute()
  85         ->first();
  86     }
  87     return $this->userJob;
  88   }
  89
  90   /**
  91    * Get submitted values stored in the user job.
  92    *
  93    * @return array
  94    * @throws \API_Exception
  95    */
  96   protected function getUserJobSubmittedValues(): array {
  97     return $this->getUserJob()['metadata']['submitted_values'];
  98   }
  99
 100   /**
 101    * Fields that may be submitted on any form in the flow.
 102    *
 103    * @var string[]
 104    */
 105   protected $submittableFields = [
 106     // Skip column header is actually a field that would be added from the
 107     // datasource - but currently only in contact, it is always there for
 108     // other imports, ditto uploadFile.
 109     'skipColumnHeader' => 'DataSource',
 110     'fieldSeparator' => 'DataSource',
 111     'uploadFile' => 'DataSource',
 112     'contactType' => 'DataSource',
 113     'contactSubType' => 'DataSource',
 114     'dateFormats' => 'DataSource',
 115     'savedMapping' => 'DataSource',
 116     'dataSource' => 'DataSource',
 117     'dedupe_rule_id' => 'DataSource',
 118     'onDuplicate' => 'DataSource',
 119     'disableUSPS' => 'DataSource',
 120     'doGeocodeAddress' => 'DataSource',
 121     'multipleCustomData' => 'DataSource',
 122     // Note we don't add the save mapping instructions for MapField here
 123     // (eg 'updateMapping') - as they really are an action for that form
 124     // rather than part of the mapping config.
 125     'mapper' => 'MapField',
 126   ];
 127
 128   /**
 129    * Get the submitted value, accessing it from whatever form in the flow it is
 130    * submitted on.
 131    *
 132    * @param string $fieldName
 133    *
 134    * @return mixed|null
 135    */
 136   public function getSubmittedValue(string $fieldName) {
 137     if ($fieldName === 'dataSource') {
 138       // Hard-coded handling for DataSource as it affects the contents of
 139       // getSubmittableFields and can cause a loop.
 140       // Note that the non-contact imports are not currently sharing the DataSource.tpl
 141       // that adds the CSV/SQL options & hence fall back on this hidden field.
 142       // - todo - switch to the same DataSource.tpl for all.
 143       return $this->controller->exportValue('DataSource', 'dataSource') ?? $this->controller->exportValue('DataSource', 'hidden_dataSource');
 144     }
 145     $mappedValues = $this->getSubmittableFields();
 146     if (array_key_exists($fieldName, $mappedValues)) {
 147       return $this->controller->exportValue($mappedValues[$fieldName], $fieldName);
 148     }
 149     return parent::getSubmittedValue($fieldName);
 150
 151   }
 152
 153   /**
 154    * Get values submitted on any form in the multi-page import flow.
 155    *
 156    * @return array
 157    */
 158   public function getSubmittedValues(): array {
 159     $values = [];
 160     foreach (array_keys($this->getSubmittableFields()) as $key) {
 161       $values[$key] = $this->getSubmittedValue($key);
 162     }
 163     return $values;
 164   }
 165
 166   /**
 167    * Get the available datasource.
 168    *
 169    * Permission dependent, this will look like
 170    * [
 171    *   'CRM_Import_DataSource_CSV' => 'Comma-Separated Values (CSV)',
 172    *   'CRM_Import_DataSource_SQL' => 'SQL Query',
 173    * ]
 174    *
 175    * The label is translated.
 176    *
 177    * @return array
 178    */
 179   protected function getDataSources(): array {
 180     $dataSources = [];
 181     foreach (['CRM_Import_DataSource_SQL', 'CRM_Import_DataSource_CSV'] as $dataSourceClass) {
 182       $object = new $dataSourceClass();
 183       if ($object->checkPermission()) {
 184         $dataSources[$dataSourceClass] = $object->getInfo()['title'];
 185       }
 186     }
 187     return $dataSources;
 188   }
 189
 190   /**
 191    * Get the name of the datasource class.
 192    *
 193    * This function prioritises retrieving from GET and POST over 'submitted'.
 194    * The reason for this is the submitted array will hold the previous submissions
 195    * data until after buildForm is called.
 196    *
 197    * This is problematic in the forward->back flow & option changing flow. As in....
 198    *
 199    * 1) Load DataSource form - initial default datasource is set to CSV and the
 200    * form is via ajax (this calls DataSourceConfig to get the data).
 201    * 2) User changes the source to SQL - the ajax updates the html but the
 202    * form was built with the expectation that the csv-specific fields would be
 203    * required.
 204    * 3) When the user submits Quickform calls preProcess and buildForm and THEN
 205    * retrieves the submitted values based on what has been added in buildForm.
 206    * Only the submitted values for fields added in buildForm are available - but
 207    * these have to be added BEFORE the submitted values are determined. Hence
 208    * we look in the POST or GET to get the updated value.
 209    *
 210    * Note that an imminent refactor will involve storing the values in the
 211    * civicrm_user_job table - this will hopefully help with a known (not new)
 212    * issue whereby the previously submitted values (eg. skipColumnHeader has
 213    * been checked or sql has been filled in) are not loaded via the ajax request.
 214    *
 215    * @return string|null
 216    *
 217    * @throws \CRM_Core_Exception
 218    */
 219   protected function getDataSourceClassName(): string {
 220     $className = CRM_Utils_Request::retrieveValue(
 221       'dataSource',
 222       'String'
 223     );
 224     if (!$className) {
 225       $className = $this->getSubmittedValue('dataSource');
 226     }
 227     if (!$className) {
 228       $className = $this->getDefaultDataSource();
 229     }
 230     if ($this->getDataSources()[$className]) {
 231       return $className;
 232     }
 233     throw new CRM_Core_Exception('Invalid data source');
 234   }
 235
 236   /**
 237    * Allow the datasource class to add fields.
 238    *
 239    * This is called as a snippet in DataSourceConfig and
 240    * also from DataSource::buildForm to add the fields such
 241    * that quick form picks them up.
 242    *
 243    * @throws \CRM_Core_Exception
 244    */
 245   protected function buildDataSourceFields(): void {
 246     $dataSourceClass = $this->getDataSourceObject();
 247     if ($dataSourceClass) {
 248       $dataSourceClass->buildQuickForm($this);
 249     }
 250   }
 251
 252   /**
 253    * Flush datasource on re-submission of the form.
 254    *
 255    * If the form has been re-submitted the datasource might have changed.
 256    * We tell the dataSource class to remove any tables (and potentially files)
 257    * created last form submission.
 258    *
 259    * If the DataSource in use is unchanged (ie still CSV or still SQL)
 260    * we also pass in the new variables. In theory it could decide that they
 261    * have not actually changed and it doesn't need to do any cleanup.
 262    *
 263    * In practice the datasource classes blast away as they always have for now
 264    * - however, the sql class, for example, might realise the fields it cares
 265    * about are unchanged and not flush the table.
 266    *
 267    * @throws \API_Exception
 268    * @throws \CRM_Core_Exception
 269    */
 270   protected function flushDataSource(): void {
 271     // If the form has been resubmitted the datasource might have changed.
 272     // We give the datasource a chance to clean up any tables it might have
 273     // created. If we are still using the same type of datasource (e.g still
 274     // an sql query
 275     $oldDataSource = $this->getUserJobSubmittedValues()['dataSource'];
 276     $oldDataSourceObject = new $oldDataSource($this->getUserJobID());
 277     $newParams = $this->getSubmittedValue('dataSource') === $oldDataSource ? $this->getSubmittedValues() : [];
 278     $oldDataSourceObject->purge($newParams);
 279   }
 280
 281   /**
 282    * Get the relevant datasource object.
 283    *
 284    * @return \CRM_Import_DataSource|null
 285    *
 286    * @throws \CRM_Core_Exception
 287    */
 288   protected function getDataSourceObject(): ?CRM_Import_DataSource {
 289     $className = $this->getDataSourceClassName();
 290     if ($className) {
 291       /* @var CRM_Import_DataSource $dataSource */
 292       return new $className($this->getUserJobID());
 293     }
 294     return NULL;
 295   }
 296
 297   /**
 298    * Allow the datasource class to add fields.
 299    *
 300    * This is called as a snippet in DataSourceConfig and
 301    * also from DataSource::buildForm to add the fields such
 302    * that quick form picks them up.
 303    */
 304   protected function getDataSourceFields(): array {
 305     $className = $this->getDataSourceClassName();
 306     if ($className) {
 307       /* @var CRM_Import_DataSource $dataSourceClass */
 308       $dataSourceClass = new $className();
 309       return $dataSourceClass->getSubmittableFields();
 310     }
 311     return [];
 312   }
 313
 314   /**
 315    * Get the default datasource.
 316    *
 317    * @return string
 318    */
 319   protected function getDefaultDataSource(): string {
 320     return 'CRM_Import_DataSource_CSV';
 321   }
 322
 323   /**
 324    * Get the fields that can be submitted in the Import form flow.
 325    *
 326    * These could be on any form in the flow & are accessed the same way from
 327    * all forms.
 328    *
 329    * @return string[]
 330    */
 331   protected function getSubmittableFields(): array {
 332     $dataSourceFields = array_fill_keys($this->getDataSourceFields(), 'DataSource');
 333     return array_merge($this->submittableFields, $dataSourceFields);
 334   }
 335
 336   /**
 337    * Get the contact type selected for the import (on the datasource form).
 338    *
 339    * @return string
 340    *   e.g Individual, Organization, Household.
 341    *
 342    * @throws \CRM_Core_Exception
 343    */
 344   protected function getContactType(): string {
 345     $contactTypeMapping = [
 346       CRM_Import_Parser::CONTACT_INDIVIDUAL => 'Individual',
 347       CRM_Import_Parser::CONTACT_HOUSEHOLD => 'Household',
 348       CRM_Import_Parser::CONTACT_ORGANIZATION => 'Organization',
 349     ];
 350     return $contactTypeMapping[$this->getSubmittedValue('contactType')];
 351   }
 352
 353   /**
 354    * Get the contact sub type selected for the import (on the datasource form).
 355    *
 356    * @return string|null
 357    *   e.g Staff.
 358    *
 359    * @throws \CRM_Core_Exception
 360    */
 361   protected function getContactSubType(): ?string {
 362     return $this->getSubmittedValue('contactSubType');
 363   }
 364
 365   /**
 366    * Create a user job to track the import.
 367    *
 368    * @return int
 369    *
 370    * @throws \API_Exception
 371    */
 372   protected function createUserJob(): int {
 373     $id = UserJob::create(FALSE)
 374       ->setValues([
 375         'created_id' => CRM_Core_Session::getLoggedInContactID(),
 376         'job_type' => $this->getUserJobType(),
 377         'status_id:name' => 'draft',
 378         // This suggests the data could be cleaned up after this.
 379         'expires_date' => '+ 1 week',
 380         'metadata' => [
 381           'submitted_values' => $this->getSubmittedValues(),
 382         ],
 383       ])
 384       ->execute()
 385       ->first()['id'];
 386     $this->setUserJobID($id);
 387     return $id;
 388   }
 389
 390   /**
 391    * @param string $key
 392    * @param array $data
 393    *
 394    * @throws \API_Exception
 395    * @throws \Civi\API\Exception\UnauthorizedException
 396    */
 397   protected function updateUserJobMetadata(string $key, array $data): void {
 398     $metaData = array_merge(
 399       $this->getUserJob()['metadata'],
 400       [$key => $data]
 401     );
 402     UserJob::update(FALSE)
 403       ->addWhere('id', '=', $this->getUserJobID())
 404       ->setValues(['metadata' => $metaData])
 405       ->execute();
 406     $this->userJob['metadata'] = $metaData;
 407   }
 408
 409   /**
 410    * Get column headers for the datasource or empty array if none apply.
 411    *
 412    * This would be the first row of a csv or the fields in an sql query.
 413    *
 414    * If the csv does not have a header row it will be empty.
 415    *
 416    * @return array
 417    *
 418    * @throws \API_Exception
 419    * @throws \CRM_Core_Exception
 420    */
 421   protected function getColumnHeaders(): array {
 422     return $this->getDataSourceObject()->getColumnHeaders();
 423   }
 424
 425   /**
 426    * Get the number of importable columns in the data source.
 427    *
 428    * @return int
 429    *
 430    * @throws \API_Exception
 431    * @throws \CRM_Core_Exception
 432    */
 433   protected function getNumberOfColumns(): int {
 434     return $this->getDataSourceObject()->getNumberOfColumns();
 435   }
 436
 437   /**
 438    * Get x data rows from the datasource.
 439    *
 440    * At this stage we are fetching from what has been stored in the form
 441    * during `postProcess` on the DataSource form.
 442    *
 443    * In the future we will use the dataSource object, likely
 444    * supporting offset as well.
 445    *
 446    * @return array|int
 447    *   One or more of the statues available - e.g
 448    *   CRM_Import_Parser::VALID
 449    *   or [CRM_Import_Parser::ERROR, CRM_Import_Parser::VALID]
 450    *
 451    * @throws \CRM_Core_Exception
 452    * @throws \API_Exception
 453    */
 454   protected function getDataRows($statuses = [], int $limit = 0): array {
 455     $statuses = (array) $statuses;
 456     return $this->getDataSourceObject()->setLimit($limit)->setStatuses($statuses)->getRows();
 457   }
 458
 459   /**
 460    * Get the datasource rows ready for csv output.
 461    *
 462    * @param array $statuses
 463    * @param int $limit
 464    *
 465    * @return array
 466    * @throws \API_Exception
 467    * @throws \CRM_Core_Exception
 468    */
 469   protected function getOutputRows($statuses = [], int $limit = 0) {
 470     $statuses = (array) $statuses;
 471     $dataSource = $this->getDataSourceObject()->setLimit($limit)->setStatuses($statuses)->setStatuses($statuses);
 472     $dataSource->setSelectFields(array_merge(['_id', '_status_message'], $dataSource->getDataFieldNames()));
 473     return $dataSource->getRows();
 474   }
 475
 476   /**
 477    * Get the column headers for the output csv.
 478    *
 479    * @return array
 480    */
 481   protected function getOutputColumnsHeaders(): array {
 482     $headers = $this->getColumnHeaders();
 483     array_unshift($headers, ts('Reason'));
 484     array_unshift($headers, ts('Line Number'));
 485     return $headers;
 486   }
 487
 488   /**
 489    * Get the number of rows with the specified status.
 490    *
 491    * @param array|int $statuses
 492    *
 493    * @return int
 494    *
 495    * @throws \API_Exception
 496    * @throws \CRM_Core_Exception
 497    */
 498   protected function getRowCount($statuses = []) {
 499     $statuses = (array) $statuses;
 500     return $this->getDataSourceObject()->getRowCount($statuses);
 501   }
 502
 503   /**
 504    * Outputs and downloads the csv of outcomes from an import job.
 505    *
 506    * This gets the rows from the temp table that match the relevant status
 507    * and output them as a csv.
 508    *
 509    * @throws \API_Exception
 510    * @throws \League\Csv\CannotInsertRecord
 511    * @throws \CRM_Core_Exception
 512    */
 513   public static function outputCSV(): void {
 514     $userJobID = CRM_Utils_Request::retrieveValue('user_job_id', 'Integer', NULL, TRUE);
 515     $status = (int) CRM_Utils_Request::retrieveValue('status', 'String', NULL, TRUE);
 516     $saveFileName = CRM_Import_Parser::saveFileName($status);
 517
 518     $form = new CRM_Import_Forms();
 519     $form->controller = new CRM_Core_Controller();
 520     $form->set('user_job_id', $userJobID);
 521
 522     $form->getUserJob();
 523     $writer = Writer::createFromFileObject(new SplTempFileObject());
 524     $headers = $form->getOutputColumnsHeaders();
 525     $writer->insertOne($headers);
 526     // Note this might be more inefficient by iterating the result
 527     // set & doing insertOne - possibly something to explore later.
 528     $writer->insertAll($form->getOutputRows($status));
 529     $writer->output($saveFileName);
 530     CRM_Utils_System::civiExit();
 531   }
 532
 533   /**
 534    * Get the url to download the relevant csv file.
 535    * @param string $status
 536    *
 537    * @return string
 538    */
 539   protected function getDownloadURL(string $status): string {
 540     return CRM_Utils_System::url('civicrm/import/outcome', [
 541       'user_job_id' => $this->get('user_job_id'),
 542       'status' => $status,
 543       'reset' => 1,
 544     ]);
 545   }
 546
 547   /**
 548    * Get the url to download the relevant csv file.
 549    * @param string $status
 550    *
 551    * @return string
 552    */
 553
 554   /**
 555    *
 556    * @return array
 557    */
 558   public function getTrackingSummary(): array {
 559     $summary = [];
 560     $fields = $this->getParser()->getTrackingFields();
 561     $row = $this->getDataSourceObject()->setAggregateFields($fields)->getRow();
 562     foreach ($fields as $fieldName => $field) {
 563       $summary[] = [
 564         'field_name' => $fieldName,
 565         'description' => $field['description'],
 566         'value' => $row[$fieldName],
 567       ];
 568     }
 569
 570     return $summary;
 571   }
 572
 573   /**
 574    * Get the fields available for import selection.
 575    *
 576    * @return array
 577    *   e.g ['first_name' => 'First Name', 'last_name' => 'Last Name'....
 578    *
 579    * @throws \API_Exception
 580    */
 581   protected function getAvailableFields(): array {
 582     return $this->getParser()->getAvailableFields();
 583   }
 584
 585   /**
 586    * Get an instance of the parser class.
 587    *
 588    * @return \CRM_Contact_Import_Parser_Contact|\CRM_Contribute_Import_Parser_Contribution
 589    */
 590   protected function getParser() {
 591     foreach (CRM_Core_BAO_UserJob::getTypes() as $jobType) {
 592       if ($jobType['id'] === $this->getUserJob()['job_type']) {
 593         $className = $jobType['class'];
 594         $classObject = new $className();
 595         $classObject->setUserJobID($this->getUserJobID());
 596         return $classObject;
 597       };
 598     }
 599     return NULL;
 600   }
 601
 602   /**
 603    * Get the mapped fields as an array of labels.
 604    *
 605    * e.g
 606    * ['First Name', 'Employee Of - First Name', 'Home - Street Address']
 607    *
 608    * @return array
 609    * @throws \API_Exception
 610    */
 611   protected function getMappedFieldLabels(): array {
 612     $mapper = [];
 613     $parser = $this->getParser();
 614     foreach ($this->getSubmittedValue('mapper') as $columnNumber => $mappedField) {
 615       $mapper[$columnNumber] = $parser->getMappedFieldLabel($parser->getMappingFieldFromMapperInput($mappedField, 0, $columnNumber));
 616     }
 617     return $mapper;
 618   }
 619
 620   /**
 621    * Assign variables required for the MapField form.
 622    *
 623    * @throws \API_Exception
 624    * @throws \CRM_Core_Exception
 625    */
 626   protected function assignMapFieldVariables(): void {
 627     $this->addExpectedSmartyVariable('highlightedRelFields');
 628     $this->_columnCount = $this->getNumberOfColumns();
 629     $this->_columnNames = $this->getColumnHeaders();
 630     $this->_dataValues = array_values($this->getDataRows([], 2));
 631     $this->assign('columnNames', $this->getColumnHeaders());
 632     $this->assign('showColumnNames', $this->getSubmittedValue('skipColumnHeader') || $this->getSubmittedValue('dataSource') !== 'CRM_Import_DataSource');
 633     $this->assign('highlightedFields', $this->getHighlightedFields());
 634     $this->assign('columnCount', $this->_columnCount);
 635     $this->assign('dataValues', $this->_dataValues);
 636   }
 637
 638   /**
 639    * Get the fields to be highlighted in the UI.
 640    *
 641    * The highlighted fields are those used to match
 642    * to an existing entity.
 643    *
 644    * @return array
 645    *
 646    * @throws \CRM_Core_Exception
 647    */
 648   protected function getHighlightedFields(): array {
 649     return [];
 650   }
 651
 652   /**
 653    * Get the data patterns to pattern match the incoming data.
 654    *
 655    * @return array
 656    */
 657   public function getDataPatterns(): array {
 658     return $this->getParser()->getDataPatterns();
 659   }
 660
 661   /**
 662    * Get the data patterns to pattern match the incoming data.
 663    *
 664    * @return array
 665    */
 666   public function getHeaderPatterns(): array {
 667     return $this->getParser()->getHeaderPatterns();
 668   }
 669
 670   /**
 671    * Has the user chosen to update existing records.
 672    * @return bool
 673    */
 674   protected function isUpdateExisting(): bool {
 675     return ((int) $this->getSubmittedValue('onDuplicate')) === CRM_Import_Parser::DUPLICATE_UPDATE;
 676   }
 677
 678   /**
 679    * Has the user chosen to update existing records.
 680    * @return bool
 681    */
 682   protected function isSkipExisting(): bool {
 683     return ((int) $this->getSubmittedValue('onDuplicate')) === CRM_Import_Parser::DUPLICATE_SKIP;
 684   }
 685
 686   /**
 687    * Are there valid rows to import.
 688    *
 689    * @return bool
 690    *
 691    * @throws \CRM_Core_Exception
 692    */
 693   protected function hasImportableRows(): bool {
 694     return (bool) $this->getRowCount(['new']);
 695   }
 696
 697 }