CRM/Utils/Migrate/ExportJSON.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 class CRM_Utils_Migrate_ExportJSON {
  18   const CHUNK_SIZE = 128;
  19
  20   protected $_contactIDs;
  21
  22   protected $_allContactIDs;
  23
  24   protected $_values;
  25
  26   protected $_discoverContacts = FALSE;
  27
  28   protected $_renameGroups = 1;
  29
  30   protected $_renameTags = 1;
  31
  32   protected $_sitePrefix = 'Site 1';
  33
  34   /**
  35    * @param array $params
  36    */
  37   public function __construct(&$params) {
  38     foreach ($params as $name => $value) {
  39       $varName = '_' . $name;
  40       $this->$varName = $value;
  41     }
  42   }
  43
  44   /**
  45    * Split a large array of contactIDs into more manageable smaller chunks.
  46    *
  47    * @param array $contactIDs
  48    *
  49    * @return array
  50    */
  51   public function &splitContactIDs(&$contactIDs) {
  52     // contactIDs could be a real large array, so we split it up into
  53     // smaller chunks and then general xml for each chunk
  54     $chunks = [];
  55     $current = 0;
  56     $chunks[$current] = [];
  57     $count = 0;
  58
  59     foreach ($contactIDs as $k => $v) {
  60       $chunks[$current][$k] = $v;
  61       $count++;
  62
  63       if ($count == self::CHUNK_SIZE) {
  64         $current++;
  65         $chunks[$current] = [];
  66         $count = 0;
  67       }
  68     }
  69
  70     if (empty($chunks[$current])) {
  71       unset($chunks[$current]);
  72     }
  73
  74     return $chunks;
  75   }
  76
  77   /**
  78    * Given a set of contact IDs get the values.
  79    *
  80    * @param array $contactIDs
  81    * @param array $additionalContactIDs
  82    */
  83   public function getValues(&$contactIDs, &$additionalContactIDs) {
  84
  85     $this->contact($contactIDs);
  86     $this->address($contactIDs);
  87     $this->phone($contactIDs);
  88     $this->email($contactIDs);
  89     $this->im($contactIDs);
  90     $this->website($contactIDs);
  91     $this->note($contactIDs);
  92
  93     $this->group($contactIDs);
  94     $this->groupContact($contactIDs);
  95     $this->savedSearch($contactIDs);
  96
  97     $this->tag($contactIDs);
  98     $this->entityTag($contactIDs);
  99
 100     $this->relationship($contactIDs, $additionalContactIDs);
 101     $this->activity($contactIDs, $additionalContactIDs);
 102   }
 103
 104   public function metaData() {
 105     $optionGroupVars = [
 106       'prefix_id' => 'individual_prefix',
 107       'suffix_id' => 'individual_suffix',
 108       'gender_id' => 'gender',
 109       'mobile_provider' => 'mobile_provider',
 110       'phone_type' => 'phone_type',
 111       'activity_type' => 'activity_type',
 112       'status_id' => 'activity_status_id',
 113       'priority_id' => 'activity_priority_id',
 114       'medium_id' => 'encounter_medium',
 115       'communication_style_id' => 'communication_style',
 116       'email_greeting' => 'email_greeting',
 117       'postal_greeting' => 'postal_greeting',
 118       'addressee_id' => 'addressee',
 119     ];
 120     $this->optionGroup($optionGroupVars);
 121
 122     $auxilaryTables = [
 123       'civicrm_location_type' => 'CRM_Core_DAO_LocationType',
 124       'civicrm_relationship_type' => 'CRM_Contact_DAO_RelationshipType',
 125     ];
 126     $this->auxTable($auxilaryTables);
 127   }
 128
 129   /**
 130    * @param array $tables
 131    */
 132   public function auxTable($tables) {
 133     foreach ($tables as $tableName => $daoName) {
 134       $fields = &$this->dbFields($daoName, TRUE);
 135
 136       $sql = "SELECT * from $tableName";
 137       $this->sql($sql, $tableName, $fields);
 138     }
 139   }
 140
 141   /**
 142    * @param array $optionGroupVars
 143    */
 144   public function optionGroup($optionGroupVars) {
 145     $names = array_values($optionGroupVars);
 146     $str = [];
 147     foreach ($names as $name) {
 148       $str[] = "'$name'";
 149     }
 150     $nameString = implode(",", $str);
 151
 152     $sql = "
 153 SELECT *
 154 FROM   civicrm_option_group
 155 WHERE  name IN ( $nameString )
 156 ";
 157     $fields = &$this->dbFields('CRM_Core_DAO_OptionGroup', TRUE);
 158     $this->sql($sql, 'civicrm_option_group', $fields);
 159
 160     $sql = "
 161 SELECT     v.*
 162 FROM       civicrm_option_value v
 163 INNER JOIN civicrm_option_group g ON v.option_group_id = g.id
 164 WHERE      g.name IN ( $nameString )
 165 ";
 166     $fields = &$this->dbFields('CRM_Core_DAO_OptionValue', TRUE);
 167     $this->sql($sql, 'civicrm_option_value', $fields);
 168   }
 169
 170   /**
 171    * @param array $ids
 172    * @param string $tableName
 173    * @param array $fields
 174    * @param string $whereField
 175    * @param string|null $additionalWhereCond
 176    */
 177   public function table(
 178     &$ids,
 179     $tableName,
 180     &$fields,
 181     $whereField,
 182     $additionalWhereCond = NULL
 183   ) {
 184     if (empty($ids)) {
 185       return;
 186     }
 187
 188     $idString = implode(',', $ids);
 189
 190     $sql = "
 191 SELECT *
 192   FROM $tableName
 193  WHERE $whereField IN ( $idString )
 194 ";
 195
 196     if ($additionalWhereCond) {
 197       $sql .= " AND $additionalWhereCond";
 198     }
 199
 200     $this->sql($sql, $tableName, $fields);
 201   }
 202
 203   /**
 204    * @param string $sql
 205    * @param string $tableName
 206    * @param array $fields
 207    */
 208   public function sql($sql, $tableName, &$fields) {
 209     $dao = &CRM_Core_DAO::executeQuery($sql);
 210
 211     while ($dao->fetch()) {
 212       $value = [];
 213       foreach ($fields as $name) {
 214         if (empty($dao->$name)) {
 215           $value[$name] = NULL;
 216         }
 217         else {
 218           $value[$name] = $dao->$name;
 219         }
 220       }
 221       $this->appendValue($dao->id, $tableName, $value);
 222     }
 223   }
 224
 225   /**
 226    * @param array $contactIDs
 227    */
 228   public function contact(&$contactIDs) {
 229     $fields = &$this->dbFields('CRM_Contact_DAO_Contact', TRUE);
 230     $this->table($contactIDs, 'civicrm_contact', $fields, 'id', NULL);
 231   }
 232
 233   /**
 234    * @param array $contactIDs
 235    */
 236   public function note(&$contactIDs) {
 237     $fields = &$this->dbFields('CRM_Core_DAO_Note', TRUE);
 238     $this->table($contactIDs, 'civicrm_note', $fields, 'entity_id', "entity_table = 'civicrm_contact'");
 239   }
 240
 241   /**
 242    * @param array $contactIDs
 243    */
 244   public function phone(&$contactIDs) {
 245     $fields = &$this->dbFields('CRM_Core_DAO_Phone', TRUE);
 246     $this->table($contactIDs, 'civicrm_phone', $fields, 'contact_id', NULL);
 247   }
 248
 249   /**
 250    * @param array $contactIDs
 251    */
 252   public function email(&$contactIDs) {
 253     $fields = &$this->dbFields('CRM_Core_DAO_Email', TRUE);
 254     $this->table($contactIDs, 'civicrm_email', $fields, 'contact_id', NULL);
 255   }
 256
 257   /**
 258    * @param array $contactIDs
 259    */
 260   public function im(&$contactIDs) {
 261     $fields = &$this->dbFields('CRM_Core_DAO_IM', TRUE);
 262     $this->table($contactIDs, 'civicrm_im', $fields, 'contact_id', NULL);
 263   }
 264
 265   /**
 266    * @param array $contactIDs
 267    */
 268   public function website(&$contactIDs) {
 269     $fields = &$this->dbFields('CRM_Core_DAO_Website', TRUE);
 270     $this->table($contactIDs, 'civicrm_website', $fields, 'contact_id', NULL);
 271   }
 272
 273   /**
 274    * @param array $contactIDs
 275    */
 276   public function address(&$contactIDs) {
 277     $fields = &$this->dbFields('CRM_Core_DAO_Email', TRUE);
 278     $this->table($contactIDs, 'civicrm_address', $fields, 'contact_id', NULL);
 279   }
 280
 281   /**
 282    * @param array $contactIDs
 283    */
 284   public function groupContact(&$contactIDs) {
 285     $fields = &$this->dbFields('CRM_Contact_DAO_GroupContact', TRUE);
 286     $this->table($contactIDs, 'civicrm_group_contact', $fields, 'contact_id', NULL);
 287   }
 288
 289   /**
 290    * @todo support group inheritance
 291    *
 292    * Parent child group ids are encoded in a text string
 293    *
 294    * @param array $contactIDs
 295    */
 296   public function group(&$contactIDs) {
 297     // handle groups only once
 298     static $_groupsHandled = [];
 299
 300     $ids = implode(',', $contactIDs);
 301
 302     $sql = "
 303 SELECT DISTINCT group_id
 304 FROM   civicrm_group_contact
 305 WHERE  contact_id IN ( $ids )
 306 ";
 307     $dao = CRM_Core_DAO::executeQuery($sql);
 308     $groupIDs = [];
 309     while ($dao->fetch()) {
 310       if (!isset($_groupsHandled[$dao->group_id])) {
 311         $groupIDs[] = $dao->group_id;
 312         $_groupsHandled[$dao->group_id] = 1;
 313       }
 314     }
 315
 316     $fields = &$this->dbFields('CRM_Contact_DAO_Group', TRUE);
 317     $this->table($groupIDs, 'civicrm_group', $fields, 'id');
 318
 319     $this->savedSearch($groupIDs);
 320   }
 321
 322   /**
 323    * @todo support search builder and custom saved searches
 324    * @param array $groupIDs
 325    */
 326   public function savedSearch(&$groupIDs) {
 327     if (empty($groupIDs)) {
 328       return;
 329     }
 330
 331     $idString = implode(",", $groupIDs);
 332     $sql = "
 333 SELECT     s.*
 334 FROM       civicrm_saved_search s
 335 INNER JOIN civicrm_group g on g.saved_search_id = s.id
 336 WHERE      g.id IN ( $idString )
 337 ";
 338
 339     $fields = &$this->dbFields('CRM_Contact_DAO_SavedSearch', TRUE);
 340     $this->sql($sql, 'civicrm_saved_search', $fields);
 341   }
 342
 343   /**
 344    * @param array $contactIDs
 345    */
 346   public function entityTag(&$contactIDs) {
 347     $fields = &$this->dbFields('CRM_Core_DAO_EntityTag', TRUE);
 348     $this->table($contactIDs, 'civicrm_entity_tag', $fields, 'entity_id', "entity_table = 'civicrm_contact'");
 349   }
 350
 351   /**
 352    * @param array $contactIDs
 353    */
 354   public function tag(&$contactIDs) {
 355     // handle tags only once
 356     static $_tagsHandled = [];
 357
 358     $ids = implode(',', $contactIDs);
 359
 360     $sql = "
 361 SELECT DISTINCT tag_id
 362 FROM   civicrm_entity_tag
 363 WHERE  entity_id IN ( $ids )
 364 AND    entity_table = 'civicrm_contact'
 365 ";
 366     $dao = CRM_Core_DAO::executeQuery($sql);
 367     $tagIDs = [];
 368     while ($dao->fetch()) {
 369       if (!isset($_tagsHandled[$dao->tag_id])) {
 370         $tagIDs[] = $dao->tag_id;
 371         $_tagsHandled[$dao->tag_id] = 1;
 372       }
 373     }
 374
 375     $fields = &$this->dbFields('CRM_Core_DAO_Tag', TRUE);
 376     $this->table($tagIDs, 'civicrm_tag', $fields, 'id');
 377   }
 378
 379   /**
 380    * @param array $contactIDs
 381    * @param $additionalContacts
 382    */
 383   public function relationship(&$contactIDs, &$additionalContacts) {
 384     // handle relationships only once
 385     static $_relationshipsHandled = [];
 386
 387     $ids = implode(',', $contactIDs);
 388
 389     $sql = "(
 390   SELECT     r.*
 391   FROM       civicrm_relationship r
 392   WHERE      r.contact_id_a IN ( $ids )
 393 ) UNION (
 394   SELECT     r.*
 395   FROM       civicrm_relationship r
 396   WHERE      r.contact_id_b IN ( $ids )
 397 )
 398 ";
 399
 400     $fields = $this->dbFields('CRM_Contact_DAO_Relationship', TRUE);
 401     $dao = &CRM_Core_DAO::executeQuery($sql);
 402     while ($dao->fetch()) {
 403       if (isset($_relationshipsHandled[$dao->id])) {
 404         continue;
 405       }
 406       $_relationshipsHandled[$dao->id] = $dao->id;
 407
 408       $relationship = [];
 409       foreach ($fields as $fld) {
 410         if (empty($dao->$fld)) {
 411           $relationship[$fld] = NULL;
 412         }
 413         else {
 414           $relationship[$fld] = $dao->$fld;
 415         }
 416       }
 417       $this->appendValue($dao->id, 'civicrm_relationship', $relationship);
 418
 419       $this->addAdditionalContacts([
 420         $dao->contact_id_a,
 421         $dao->contact_id_b,
 422       ],
 423         $additionalContacts
 424       );
 425     }
 426   }
 427
 428   /**
 429    * @param array $contactIDs
 430    * @param $additionalContacts
 431    */
 432   public function activity(&$contactIDs, &$additionalContacts) {
 433     static $_activitiesHandled = [];
 434     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 435     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 436     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 437     $ids = implode(',', $contactIDs);
 438
 439     // query framing returning all contacts in valid activity
 440     $sql = "
 441 SELECT  a.*, ac.id as acID, ac.activity_id, ac.contact_id, ac.record_type_id
 442 FROM civicrm_activity a
 443 INNER JOIN civicrm_activity_contact ac ON ac.activity_id = a.id
 444 WHERE ac.contact_id IN ( $ids )
 445   AND (a.activity_type_id != 3 AND a.activity_type_id != 20)
 446 ";
 447
 448     $fields = &$this->dbFields('CRM_Activity_DAO_Activity', TRUE);
 449
 450     $dao = &CRM_Core_DAO::executeQuery($sql);
 451     while ($dao->fetch()) {
 452       // adding source, target and assignee contacts in additional contacts array
 453       $this->addAdditionalContacts([$dao->contact_id],
 454         $additionalContacts
 455       );
 456
 457       // append values of activity contacts
 458       $activityContacts = [
 459         'id' => $dao->acID,
 460         'contact_id' => $dao->contact_id,
 461         'activity_id' => $dao->activity_id,
 462         'record_type_id' => $dao->record_type_id,
 463       ];
 464       $this->appendValue($dao->acID, 'civicrm_activity_contact', $activityContacts);
 465
 466       if (isset($_activitiesHandled[$dao->id])) {
 467         continue;
 468       }
 469       $_activitiesHandled[$dao->id] = $dao->id;
 470
 471       $activity = [];
 472       foreach ($fields as $fld) {
 473         if (empty($dao->$fld)) {
 474           $activity[$fld] = NULL;
 475         }
 476         else {
 477           $activity[$fld] = $dao->$fld;
 478         }
 479       }
 480
 481       // append activity value
 482       $this->appendValue($dao->id, 'civicrm_activity', $activity);
 483     }
 484   }
 485
 486   /**
 487    * @param int $id
 488    * @param string $name
 489    * @param array $value
 490    */
 491   public function appendValue($id, $name, $value) {
 492     if (empty($value)) {
 493       return;
 494     }
 495
 496     if (!isset($this->_values[$name])) {
 497       $this->_values[$name] = [];
 498       $this->_values[$name][] = array_keys($value);
 499     }
 500     $this->_values[$name][] = array_values($value);
 501   }
 502
 503   /**
 504    * @param string $daoName
 505    * @param bool $onlyKeys
 506    *
 507    * @return array
 508    */
 509   public function dbFields($daoName, $onlyKeys = FALSE) {
 510     static $_fieldsRetrieved = [];
 511
 512     if (!isset($_fieldsRetrieved[$daoName])) {
 513       $_fieldsRetrieved[$daoName] = [];
 514       $daoFile = str_replace('_',
 515           DIRECTORY_SEPARATOR,
 516           $daoName
 517         ) . '.php';
 518       include_once $daoFile;
 519
 520       $daoFields = &$daoName::fields();
 521
 522       foreach ($daoFields as $key => & $value) {
 523         $_fieldsRetrieved[$daoName][$value['name']] = [
 524           'uniqueName' => $key,
 525           'type' => $value['type'],
 526           'title' => $value['title'] ?? NULL,
 527         ];
 528       }
 529     }
 530
 531     if ($onlyKeys) {
 532       return array_keys($_fieldsRetrieved[$daoName]);
 533     }
 534     else {
 535       return $_fieldsRetrieved[$daoName];
 536     }
 537   }
 538
 539   /**
 540    * @param array $contactIDs
 541    * @param array $additionalContacts
 542    */
 543   public function addAdditionalContacts($contactIDs, &$additionalContacts) {
 544     if (!$this->_discoverContacts) {
 545       return;
 546     }
 547
 548     foreach ($contactIDs as $cid) {
 549       if ($cid &&
 550         !isset($this->_allContactIDs[$cid]) &&
 551         !isset($additionalContacts[$cid])
 552       ) {
 553         $additionalContacts[$cid] = $cid;
 554       }
 555     }
 556   }
 557
 558   /**
 559    * @param array $contactIDs
 560    */
 561   public function export(&$contactIDs) {
 562     $chunks = &$this->splitContactIDs($contactIDs);
 563
 564     $additionalContactIDs = [];
 565
 566     foreach ($chunks as $chunk) {
 567       $this->getValues($chunk, $additionalContactIDs);
 568     }
 569
 570     if (!empty($additionalContactIDs)) {
 571       $this->_allContactIDs = $this->_allContactIDs + $additionalContactIDs;
 572       $this->export($additionalContactIDs);
 573     }
 574   }
 575
 576   /**
 577    * @param string $fileName
 578    * @param string $lastExportTime
 579    * @param bool $discoverContacts
 580    */
 581   public function run(
 582     $fileName,
 583     $lastExportTime = NULL,
 584     $discoverContacts = FALSE
 585   ) {
 586     $this->_discoverContacts = $discoverContacts;
 587
 588     if (!$lastExportTime) {
 589       $sql = "
 590 SELECT id
 591 FROM   civicrm_contact
 592 ";
 593     }
 594     else {
 595       $sql = "(
 596 SELECT DISTINCT entity_id
 597 FROM   civicrm_log
 598 WHERE  entity_table = 'civicrm_contact'
 599 AND    modified_date >= $lastExportTime
 600 ) UNION (
 601 SELECT DISTINCT contact_id
 602 FROM   civicrm_subscription_history
 603 WHERE  date >= $lastExportTime
 604 )
 605 ";
 606     }
 607
 608     $dao = &CRM_Core_DAO::executeQuery($sql);
 609
 610     $contactIDs = [];
 611     while ($dao->fetch()) {
 612       $contactIDs[$dao->id] = $dao->id;
 613     }
 614
 615     $this->_allContactIDs = $contactIDs;
 616     $this->_values = [];
 617
 618     $this->metaData();
 619
 620     $this->export($contactIDs);
 621
 622     $json = json_encode($this->_values, JSON_NUMERIC_CHECK);
 623     file_put_contents($fileName,
 624       $json
 625     );
 626
 627     // print_r( json_decode( $json ) );
 628   }
 629
 630 }