CRM/Export/BAO/ExportProcessor.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2018
  32  */
  33
  34 /**
  35  * Class CRM_Export_BAO_ExportProcessor
  36  *
  37  * Class to handle logic of export.
  38  */
  39 class CRM_Export_BAO_ExportProcessor {
  40
  41   /**
  42    * @var int
  43    */
  44   protected $queryMode;
  45
  46   /**
  47    * @var int
  48    */
  49   protected $exportMode;
  50
  51   /**
  52    * Array of fields in the main query.
  53    *
  54    * @var array
  55    */
  56   protected $queryFields = [];
  57
  58   /**
  59    * Either AND or OR.
  60    *
  61    * @var string
  62    */
  63   protected $queryOperator;
  64
  65   /**
  66    * Requested output fields.
  67    *
  68    * If set to NULL then it is 'primary fields only'
  69    * which actually means pretty close to all fields!
  70    *
  71    * @var array|null
  72    */
  73   protected $requestedFields;
  74
  75   /**
  76    * Key representing the head of household in the relationship array.
  77    *
  78    * e.g. ['8_b_a' => 'Household Member Is', '8_a_b = 'Household Member Of'.....]
  79    *
  80    * @var
  81    */
  82   protected $relationshipTypes = [];
  83
  84   /**
  85    * Array of properties to retrieve for relationships.
  86    *
  87    * @var array
  88    */
  89   protected $relationshipReturnProperties = [];
  90
  91   /**
  92    * @var array
  93    */
  94   protected $returnProperties = [];
  95
  96   /**
  97    * CRM_Export_BAO_ExportProcessor constructor.
  98    *
  99    * @param int $exportMode
 100    * @param array|NULL $requestedFields
 101    * @param string $queryOperator
 102    */
 103   public function __construct($exportMode, $requestedFields, $queryOperator) {
 104     $this->setExportMode($exportMode);
 105     $this->setQueryMode();
 106     $this->setQueryOperator($queryOperator);
 107     $this->setRequestedFields($requestedFields);
 108     $this->setRelationshipTypes();
 109   }
 110
 111   /**
 112    * @return array|null
 113    */
 114   public function getRequestedFields() {
 115     return $this->requestedFields;
 116   }
 117
 118   /**
 119    * @param array|null $requestedFields
 120    */
 121   public function setRequestedFields($requestedFields) {
 122     $this->requestedFields = $requestedFields;
 123   }
 124
 125
 126   /**
 127    * @return array
 128    */
 129   public function getReturnProperties() {
 130     return $this->returnProperties;
 131   }
 132
 133   /**
 134    * @param array $returnProperties
 135    */
 136   public function setReturnProperties($returnProperties) {
 137     $this->returnProperties = $returnProperties;
 138   }
 139
 140   /**
 141    * @return array
 142    */
 143   public function getRelationshipTypes() {
 144     return $this->relationshipTypes;
 145   }
 146
 147   /**
 148    */
 149   public function setRelationshipTypes() {
 150     $this->relationshipTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(
 151       NULL,
 152       NULL,
 153       NULL,
 154       NULL,
 155       TRUE,
 156       'name',
 157       FALSE
 158     );
 159   }
 160
 161
 162   /**
 163    * @param $fieldName
 164    * @return bool
 165    */
 166   public function isRelationshipTypeKey($fieldName) {
 167     return array_key_exists($fieldName, $this->relationshipTypes);
 168   }
 169
 170   /**
 171    * @return string
 172    */
 173   public function getQueryOperator() {
 174     return $this->queryOperator;
 175   }
 176
 177   /**
 178    * @param string $queryOperator
 179    */
 180   public function setQueryOperator($queryOperator) {
 181     $this->queryOperator = $queryOperator;
 182   }
 183
 184   /**
 185    * @return array
 186    */
 187   public function getQueryFields() {
 188     return $this->queryFields;
 189   }
 190
 191   /**
 192    * @param array $queryFields
 193    */
 194   public function setQueryFields($queryFields) {
 195     $this->queryFields = $queryFields;
 196   }
 197
 198   /**
 199    * @return int
 200    */
 201   public function getQueryMode() {
 202     return $this->queryMode;
 203   }
 204
 205   /**
 206    * Set the query mode based on the export mode.
 207    */
 208   public function setQueryMode() {
 209
 210     switch ($this->getExportMode()) {
 211       case CRM_Export_Form_Select::CONTRIBUTE_EXPORT:
 212         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTRIBUTE;
 213         break;
 214
 215       case CRM_Export_Form_Select::EVENT_EXPORT:
 216         $this->queryMode = CRM_Contact_BAO_Query::MODE_EVENT;
 217         break;
 218
 219       case CRM_Export_Form_Select::MEMBER_EXPORT:
 220         $this->queryMode = CRM_Contact_BAO_Query::MODE_MEMBER;
 221         break;
 222
 223       case CRM_Export_Form_Select::PLEDGE_EXPORT:
 224         $this->queryMode = CRM_Contact_BAO_Query::MODE_PLEDGE;
 225         break;
 226
 227       case CRM_Export_Form_Select::CASE_EXPORT:
 228         $this->queryMode = CRM_Contact_BAO_Query::MODE_CASE;
 229         break;
 230
 231       case CRM_Export_Form_Select::GRANT_EXPORT:
 232         $this->queryMode = CRM_Contact_BAO_Query::MODE_GRANT;
 233         break;
 234
 235       case CRM_Export_Form_Select::ACTIVITY_EXPORT:
 236         $this->queryMode = CRM_Contact_BAO_Query::MODE_ACTIVITY;
 237         break;
 238
 239       default:
 240         $this->queryMode = CRM_Contact_BAO_Query::MODE_CONTACTS;
 241     }
 242   }
 243
 244   /**
 245    * @return int
 246    */
 247   public function getExportMode() {
 248     return $this->exportMode;
 249   }
 250
 251   /**
 252    * @param int $exportMode
 253    */
 254   public function setExportMode($exportMode) {
 255     $this->exportMode = $exportMode;
 256   }
 257
 258   /**
 259    * @param $params
 260    * @param $order
 261    * @param $returnProperties
 262    * @return array
 263    */
 264   public function runQuery($params, $order, $returnProperties) {
 265     $query = new CRM_Contact_BAO_Query($params, $returnProperties, NULL,
 266       FALSE, FALSE, $this->getQueryMode(),
 267       FALSE, TRUE, TRUE, NULL, $this->getQueryOperator()
 268     );
 269
 270     //sort by state
 271     //CRM-15301
 272     $query->_sort = $order;
 273     list($select, $from, $where, $having) = $query->query();
 274     $this->setQueryFields($query->_fields);
 275     return array($query, $select, $from, $where, $having);
 276   }
 277
 278   /**
 279    * Get array of fields to return, over & above those defined in the main contact exportable fields.
 280    *
 281    * These include export mode specific fields & some fields apparently required as 'exportableFields'
 282    * but not returned by the function of the same name.
 283    *
 284    * @return array
 285    *   Array of fields to return in the format ['field_name' => 1,...]
 286    */
 287   public function getAdditionalReturnProperties() {
 288
 289     $missing = [
 290       'location_type',
 291       'im_provider',
 292       'phone_type_id',
 293       'provider_id',
 294       'current_employer',
 295     ];
 296     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) {
 297       $componentSpecificFields = [];
 298     }
 299     else {
 300       $componentSpecificFields = CRM_Contact_BAO_Query::defaultReturnProperties($this->getQueryMode());
 301     }
 302     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_PLEDGE) {
 303       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Pledge_BAO_Query::extraReturnProperties($this->getQueryMode()));
 304       unset($componentSpecificFields['contribution_status_id']);
 305       unset($componentSpecificFields['pledge_status_id']);
 306       unset($componentSpecificFields['pledge_payment_status_id']);
 307     }
 308     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CASE) {
 309       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Case_BAO_Query::extraReturnProperties($this->getQueryMode()));
 310     }
 311     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTRIBUTE) {
 312       $componentSpecificFields = array_merge($componentSpecificFields, CRM_Contribute_BAO_Query::softCreditReturnProperties(TRUE));
 313       unset($componentSpecificFields['contribution_status_id']);
 314     }
 315     return array_merge(array_fill_keys($missing, 1), $componentSpecificFields);
 316   }
 317
 318   /**
 319    * Should payment fields be appended to the export.
 320    *
 321    * (This is pretty hacky so hopefully this function won't last long - notice
 322    * how obviously it should be part of the above function!).
 323    */
 324   public function isExportPaymentFields() {
 325     if ($this->getRequestedFields() === NULL
 326       &&  in_array($this->getQueryMode(), [
 327         CRM_Contact_BAO_Query::MODE_EVENT,
 328         CRM_Contact_BAO_Query::MODE_MEMBER,
 329         CRM_Contact_BAO_Query::MODE_PLEDGE,
 330       ])) {
 331       return TRUE;
 332     }
 333     elseif ($this->isExportSpecifiedPaymentFields()) {
 334       return TRUE;
 335     }
 336     return FALSE;
 337   }
 338
 339   /**
 340    * Has specific payment fields been requested (as opposed to via all fields).
 341    *
 342    * If specific fields have been requested then they get added at various points.
 343    *
 344    * @return bool
 345    */
 346   public function isExportSpecifiedPaymentFields() {
 347     if ($this->getRequestedFields() !== NULL && $this->hasRequestedComponentPaymentFields()) {
 348       return TRUE;
 349     }
 350   }
 351
 352   /**
 353    * Get the name of the id field in the table that connects contributions to the export entity.
 354    */
 355   public function getPaymentTableID() {
 356     if ($this->getRequestedFields() === NULL) {
 357       $mapping = [
 358         CRM_Contact_BAO_Query::MODE_EVENT => 'participant_id',
 359         CRM_Contact_BAO_Query::MODE_MEMBER => 'membership_id',
 360         CRM_Contact_BAO_Query::MODE_PLEDGE => 'pledge_payment_id',
 361       ];
 362       return isset($mapping[$this->getQueryMode()]) ? $mapping[$this->getQueryMode()] : '';
 363     }
 364     elseif ($this->hasRequestedComponentPaymentFields()) {
 365       return 'participant_id';
 366     }
 367     return FALSE;
 368   }
 369
 370   /**
 371    * Have component payment fields been requested.
 372    *
 373    * @return bool
 374    */
 375   protected function hasRequestedComponentPaymentFields() {
 376     if ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_EVENT) {
 377       $participantPaymentFields = array_intersect_key($this->getComponentPaymentFields(), $this->getReturnProperties());
 378       if (!empty($participantPaymentFields)) {
 379         return TRUE;
 380       }
 381     }
 382     return FALSE;
 383   }
 384
 385   /**
 386    * Get fields that indicate payment fields have been requested for a component.
 387    *
 388    * @return array
 389    */
 390   protected function getComponentPaymentFields() {
 391     return [
 392       'componentPaymentField_total_amount' => ts('Total Amount'),
 393       'componentPaymentField_contribution_status' => ts('Contribution Status'),
 394       'componentPaymentField_received_date' => ts('Date Received'),
 395       'componentPaymentField_payment_instrument' => ts('Payment Method'),
 396       'componentPaymentField_transaction_id' => ts('Transaction ID'),
 397     ];
 398   }
 399
 400   /**
 401    * Get the default properties when not specified.
 402    *
 403    * In the UI this appears as 'Primary fields only' but in practice it's
 404    * most of the kitchen sink and the hallway closet thrown in.
 405    *
 406    * Since CRM-952 custom fields are excluded, but no other form of mercy is shown.
 407    *
 408    * @return array
 409    */
 410   public function getDefaultReturnProperties() {
 411     $returnProperties = [];
 412     $fields = CRM_Contact_BAO_Contact::exportableFields('All', TRUE, TRUE);
 413     $skippedFields = ($this->getQueryMode() === CRM_Contact_BAO_Query::MODE_CONTACTS) ? [] : [
 414       'groups',
 415       'tags',
 416       'notes'
 417     ];
 418
 419     foreach ($fields as $key => $var) {
 420       if ($key && (substr($key, 0, 6) != 'custom') && !in_array($key, $skippedFields)) {
 421         $returnProperties[$key] = 1;
 422       }
 423     }
 424     $returnProperties = array_merge($returnProperties, $this->getAdditionalReturnProperties());
 425     return $returnProperties;
 426   }
 427
 428   /**
 429    * Add the field to relationship return properties & return it.
 430    *
 431    * This function is doing both setting & getting which is yuck but it is an interim
 432    * refactor.
 433    *
 434    * @param array $value
 435    * @param string $relationshipKey
 436    *
 437    * @return array
 438    */
 439   public function setRelationshipReturnProperties($value, $relationshipKey) {
 440     $locationTypes = CRM_Core_PseudoConstant::get('CRM_Core_DAO_Address', 'location_type_id');
 441     $relPhoneTypeId = $relIMProviderId = NULL;
 442     if (!empty($value[2])) {
 443       $relationField = CRM_Utils_Array::value(2, $value);
 444       if (trim(CRM_Utils_Array::value(3, $value))) {
 445         $relLocTypeId = CRM_Utils_Array::value(3, $value);
 446       }
 447       else {
 448         $relLocTypeId = 'Primary';
 449       }
 450
 451       if ($relationField == 'phone') {
 452         $relPhoneTypeId = CRM_Utils_Array::value(4, $value);
 453       }
 454       elseif ($relationField == 'im') {
 455         $relIMProviderId = CRM_Utils_Array::value(4, $value);
 456       }
 457     }
 458     elseif (!empty($value[4])) {
 459       $relationField = CRM_Utils_Array::value(4, $value);
 460       $relLocTypeId = CRM_Utils_Array::value(5, $value);
 461       if ($relationField == 'phone') {
 462         $relPhoneTypeId = CRM_Utils_Array::value(6, $value);
 463       }
 464       elseif ($relationField == 'im') {
 465         $relIMProviderId = CRM_Utils_Array::value(6, $value);
 466       }
 467     }
 468     if (in_array($relationField, $this->getValidLocationFields()) && is_numeric($relLocTypeId)) {
 469       if ($relPhoneTypeId) {
 470         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationTypes[$relLocTypeId]]['phone-' . $relPhoneTypeId] = 1;
 471       }
 472       elseif ($relIMProviderId) {
 473         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationTypes[$relLocTypeId]]['im-' . $relIMProviderId] = 1;
 474       }
 475       else {
 476         $this->relationshipReturnProperties[$relationshipKey]['location'][$locationTypes[$relLocTypeId]][$relationField] = 1;
 477       }
 478     }
 479     else {
 480       $this->relationshipReturnProperties[$relationshipKey][$relationField] = 1;
 481     }
 482     return $this->relationshipReturnProperties[$relationshipKey];
 483   }
 484
 485   /**
 486    * Get the default location fields to request.
 487    *
 488    * @return array
 489    */
 490   public function getValidLocationFields() {
 491     return [
 492       'street_address',
 493       'supplemental_address_1',
 494       'supplemental_address_2',
 495       'supplemental_address_3',
 496       'city',
 497       'postal_code',
 498       'postal_code_suffix',
 499       'geo_code_1',
 500       'geo_code_2',
 501       'state_province',
 502       'country',
 503       'phone',
 504       'email',
 505       'im',
 506     ];
 507   }
 508
 509   /**
 510    * Get the sql column definition for the given field.
 511    *
 512    * @param $field
 513    *
 514    * @return mixed
 515    */
 516   public function getSqlColumnDefinition($field) {
 517     $fieldName = $this->getMungedFieldName($field);
 518
 519     // early exit for master_id, CRM-12100
 520     // in the DB it is an ID, but in the export, we retrive the display_name of the master record
 521     // also for current_employer, CRM-16939
 522     if ($fieldName == 'master_id' || $fieldName == 'current_employer') {
 523       return "$fieldName varchar(128)";
 524     }
 525
 526     if (substr($fieldName, -11) == 'campaign_id') {
 527       // CRM-14398
 528       return "$fieldName varchar(128)";
 529     }
 530
 531     $queryFields = $this->getQueryFields();
 532     $lookUp = ['prefix_id', 'suffix_id'];
 533     // set the sql columns
 534     if (isset($queryFields[$field]['type'])) {
 535       switch ($queryFields[$field]['type']) {
 536         case CRM_Utils_Type::T_INT:
 537         case CRM_Utils_Type::T_BOOLEAN:
 538           if (in_array($field, $lookUp)) {
 539             return "$fieldName varchar(255)";
 540           }
 541           else {
 542             return "$fieldName varchar(16)";
 543           }
 544
 545         case CRM_Utils_Type::T_STRING:
 546           if (isset($queryFields[$field]['maxlength'])) {
 547             return "$fieldName varchar({$queryFields[$field]['maxlength']})";
 548           }
 549           else {
 550             return "$fieldName varchar(255)";
 551           }
 552
 553         case CRM_Utils_Type::T_TEXT:
 554         case CRM_Utils_Type::T_LONGTEXT:
 555         case CRM_Utils_Type::T_BLOB:
 556         case CRM_Utils_Type::T_MEDIUMBLOB:
 557           return "$fieldName longtext";
 558
 559         case CRM_Utils_Type::T_FLOAT:
 560         case CRM_Utils_Type::T_ENUM:
 561         case CRM_Utils_Type::T_DATE:
 562         case CRM_Utils_Type::T_TIME:
 563         case CRM_Utils_Type::T_TIMESTAMP:
 564         case CRM_Utils_Type::T_MONEY:
 565         case CRM_Utils_Type::T_EMAIL:
 566         case CRM_Utils_Type::T_URL:
 567         case CRM_Utils_Type::T_CCNUM:
 568         default:
 569           return "$fieldName varchar(32)";
 570       }
 571     }
 572     else {
 573       if (substr($fieldName, -3, 3) == '_id') {
 574         return "$fieldName varchar(255)";
 575       }
 576       elseif (substr($fieldName, -5, 5) == '_note') {
 577         return "$fieldName text";
 578       }
 579       else {
 580         $changeFields = [
 581           'groups',
 582           'tags',
 583           'notes',
 584         ];
 585
 586         if (in_array($fieldName, $changeFields)) {
 587           return "$fieldName text";
 588         }
 589         else {
 590           // set the sql columns for custom data
 591           if (isset($queryFields[$field]['data_type'])) {
 592
 593             switch ($queryFields[$field]['data_type']) {
 594               case 'String':
 595                 // May be option labels, which could be up to 512 characters
 596                 $length = max(512, CRM_Utils_Array::value('text_length', $queryFields[$field]));
 597                 return "$fieldName varchar($length)";
 598
 599               case 'Country':
 600               case 'StateProvince':
 601               case 'Link':
 602                 return "$fieldName varchar(255)";
 603
 604               case 'Memo':
 605                 return "$fieldName text";
 606
 607               default:
 608                 return "$fieldName varchar(255)";
 609             }
 610           }
 611           else {
 612             return "$fieldName text";
 613           }
 614         }
 615       }
 616     }
 617   }
 618
 619   /**
 620    * Get the munged field name.
 621    *
 622    * @param string $field
 623    * @return string
 624    */
 625   public function getMungedFieldName($field) {
 626     $fieldName = CRM_Utils_String::munge(strtolower($field), '_', 64);
 627     if ($fieldName == 'id') {
 628       $fieldName = 'civicrm_primary_id';
 629     }
 630     return $fieldName;
 631   }
 632
 633 }