CRM/Utils/Migrate/ExportJSON.php

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