CRM/Utils/Migrate/ExportJSON.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 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 = array();
  71     $current = 0;
  72     $chunks[$current] = array();
  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] = array();
  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 = array(
 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 = array(
 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 = array();
 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 = array();
 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     $dao->free();
 240   }
 241
 242   /**
 243    * @param $contactIDs
 244    */
 245   public function contact(&$contactIDs) {
 246     $fields = &$this->dbFields('CRM_Contact_DAO_Contact', TRUE);
 247     $this->table($contactIDs, 'civicrm_contact', $fields, 'id', NULL);
 248   }
 249
 250   /**
 251    * @param $contactIDs
 252    */
 253   public function note(&$contactIDs) {
 254     $fields = &$this->dbFields('CRM_Core_DAO_Note', TRUE);
 255     $this->table($contactIDs, 'civicrm_note', $fields, 'entity_id', "entity_table = 'civicrm_contact'");
 256   }
 257
 258   /**
 259    * @param $contactIDs
 260    */
 261   public function phone(&$contactIDs) {
 262     $fields = &$this->dbFields('CRM_Core_DAO_Phone', TRUE);
 263     $this->table($contactIDs, 'civicrm_phone', $fields, 'contact_id', NULL);
 264   }
 265
 266   /**
 267    * @param $contactIDs
 268    */
 269   public function email(&$contactIDs) {
 270     $fields = &$this->dbFields('CRM_Core_DAO_Email', TRUE);
 271     $this->table($contactIDs, 'civicrm_email', $fields, 'contact_id', NULL);
 272   }
 273
 274   /**
 275    * @param $contactIDs
 276    */
 277   public function im(&$contactIDs) {
 278     $fields = &$this->dbFields('CRM_Core_DAO_IM', TRUE);
 279     $this->table($contactIDs, 'civicrm_im', $fields, 'contact_id', NULL);
 280   }
 281
 282   /**
 283    * @param $contactIDs
 284    */
 285   public function website(&$contactIDs) {
 286     $fields = &$this->dbFields('CRM_Core_DAO_Website', TRUE);
 287     $this->table($contactIDs, 'civicrm_website', $fields, 'contact_id', NULL);
 288   }
 289
 290   /**
 291    * @param $contactIDs
 292    */
 293   public function address(&$contactIDs) {
 294     $fields = &$this->dbFields('CRM_Core_DAO_Email', TRUE);
 295     $this->table($contactIDs, 'civicrm_address', $fields, 'contact_id', NULL);
 296   }
 297
 298   /**
 299    * @param $contactIDs
 300    */
 301   public function groupContact(&$contactIDs) {
 302     $fields = &$this->dbFields('CRM_Contact_DAO_GroupContact', TRUE);
 303     $this->table($contactIDs, 'civicrm_group_contact', $fields, 'contact_id', NULL);
 304   }
 305
 306   /**
 307    * @todo support group inheritance
 308    *
 309    * Parent child group ids are encoded in a text string
 310    *
 311    * @param $contactIDs
 312    */
 313   public function group(&$contactIDs) {
 314     // handle groups only once
 315     static $_groupsHandled = array();
 316
 317     $ids = implode(',', $contactIDs);
 318
 319     $sql = "
 320 SELECT DISTINCT group_id
 321 FROM   civicrm_group_contact
 322 WHERE  contact_id IN ( $ids )
 323 ";
 324     $dao = CRM_Core_DAO::executeQuery($sql);
 325     $groupIDs = array();
 326     while ($dao->fetch()) {
 327       if (!isset($_groupsHandled[$dao->group_id])) {
 328         $groupIDs[] = $dao->group_id;
 329         $_groupsHandled[$dao->group_id] = 1;
 330       }
 331     }
 332
 333     $fields = &$this->dbFields('CRM_Contact_DAO_Group', TRUE);
 334     $this->table($groupIDs, 'civicrm_group', $fields, 'id');
 335
 336     $this->savedSearch($groupIDs);
 337   }
 338
 339   /**
 340    * @todo support search builder and custom saved searches
 341    * @param $groupIDs
 342    */
 343   public function savedSearch(&$groupIDs) {
 344     if (empty($groupIDs)) {
 345       return;
 346     }
 347
 348     $idString = implode(",", $groupIDs);
 349     $sql = "
 350 SELECT     s.*
 351 FROM       civicrm_saved_search s
 352 INNER JOIN civicrm_group g on g.saved_search_id = s.id
 353 WHERE      g.id IN ( $idString )
 354 ";
 355
 356     $fields = &$this->dbFields('CRM_Contact_DAO_SavedSearch', TRUE);
 357     $this->sql($sql, 'civicrm_saved_search', $fields);
 358   }
 359
 360   /**
 361    * @param $contactIDs
 362    */
 363   public function entityTag(&$contactIDs) {
 364     $fields = &$this->dbFields('CRM_Core_DAO_EntityTag', TRUE);
 365     $this->table($contactIDs, 'civicrm_entity_tag', $fields, 'entity_id', "entity_table = 'civicrm_contact'");
 366   }
 367
 368   /**
 369    * @param $contactIDs
 370    */
 371   public function tag(&$contactIDs) {
 372     // handle tags only once
 373     static $_tagsHandled = array();
 374
 375     $ids = implode(',', $contactIDs);
 376
 377     $sql = "
 378 SELECT DISTINCT tag_id
 379 FROM   civicrm_entity_tag
 380 WHERE  entity_id IN ( $ids )
 381 AND    entity_table = 'civicrm_contact'
 382 ";
 383     $dao = CRM_Core_DAO::executeQuery($sql);
 384     $tagIDs = array();
 385     while ($dao->fetch()) {
 386       if (!isset($_tagsHandled[$dao->tag_id])) {
 387         $tagIDs[] = $dao->tag_id;
 388         $_tagsHandled[$dao->tag_id] = 1;
 389       }
 390     }
 391
 392     $fields = &$this->dbFields('CRM_Core_DAO_Tag', TRUE);
 393     $this->table($tagIDs, 'civicrm_tag', $fields, 'id');
 394   }
 395
 396   /**
 397    * @param $contactIDs
 398    * @param $additionalContacts
 399    */
 400   public function relationship(&$contactIDs, &$additionalContacts) {
 401     // handle relationships only once
 402     static $_relationshipsHandled = array();
 403
 404     $ids = implode(',', $contactIDs);
 405
 406     $sql = "(
 407   SELECT     r.*
 408   FROM       civicrm_relationship r
 409   WHERE      r.contact_id_a IN ( $ids )
 410 ) UNION (
 411   SELECT     r.*
 412   FROM       civicrm_relationship r
 413   WHERE      r.contact_id_b IN ( $ids )
 414 )
 415 ";
 416
 417     $fields = $this->dbFields('CRM_Contact_DAO_Relationship', TRUE);
 418     $dao = &CRM_Core_DAO::executeQuery($sql);
 419     while ($dao->fetch()) {
 420       if (isset($_relationshipsHandled[$dao->id])) {
 421         continue;
 422       }
 423       $_relationshipsHandled[$dao->id] = $dao->id;
 424
 425       $relationship = array();
 426       foreach ($fields as $fld) {
 427         if (empty($dao->$fld)) {
 428           $relationship[$fld] = NULL;
 429         }
 430         else {
 431           $relationship[$fld] = $dao->$fld;
 432         }
 433       }
 434       $this->appendValue($dao->id, 'civicrm_relationship', $relationship);
 435
 436       $this->addAdditionalContacts(array(
 437           $dao->contact_id_a,
 438           $dao->contact_id_b,
 439         ),
 440         $additionalContacts
 441       );
 442     }
 443     $dao->free();
 444   }
 445
 446   /**
 447    * @param $contactIDs
 448    * @param $additionalContacts
 449    */
 450   public function activity(&$contactIDs, &$additionalContacts) {
 451     static $_activitiesHandled = array();
 452     $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 453     $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 454     $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 455     $ids = implode(',', $contactIDs);
 456
 457     // query framing returning all contacts in valid activity
 458     $sql = "
 459 SELECT  a.*, ac.id as acID, ac.activity_id, ac.contact_id, ac.record_type_id
 460 FROM civicrm_activity a
 461 INNER JOIN civicrm_activity_contact ac ON ac.activity_id = a.id
 462 WHERE ac.contact_id IN ( $ids )
 463   AND (a.activity_type_id != 3 AND a.activity_type_id != 20)
 464 ";
 465
 466     $fields = &$this->dbFields('CRM_Activity_DAO_Activity', TRUE);
 467
 468     $dao = &CRM_Core_DAO::executeQuery($sql);
 469     while ($dao->fetch()) {
 470       // adding source, target and assignee contacts in additional contacts array
 471       $this->addAdditionalContacts(array($dao->contact_id),
 472         $additionalContacts
 473       );
 474
 475       // append values of activity contacts
 476       $activityContacts = array(
 477         'id' => $dao->acID,
 478         'contact_id' => $dao->contact_id,
 479         'activity_id' => $dao->activity_id,
 480         'record_type_id' => $dao->record_type_id,
 481       );
 482       $this->appendValue($dao->acID, 'civicrm_activity_contact', $activityContacts);
 483
 484       if (isset($_activitiesHandled[$dao->id])) {
 485         continue;
 486       }
 487       $_activitiesHandled[$dao->id] = $dao->id;
 488
 489       $activity = array();
 490       foreach ($fields as $fld) {
 491         if (empty($dao->$fld)) {
 492           $activity[$fld] = NULL;
 493         }
 494         else {
 495           $activity[$fld] = $dao->$fld;
 496         }
 497       }
 498
 499       // append activity value
 500       $this->appendValue($dao->id, 'civicrm_activity', $activity);
 501     }
 502     $dao->free();
 503   }
 504
 505   /**
 506    * @param int $id
 507    * @param string $name
 508    * @param $value
 509    */
 510   public function appendValue($id, $name, $value) {
 511     if (empty($value)) {
 512       return;
 513     }
 514
 515     if (!isset($this->_values[$name])) {
 516       $this->_values[$name] = array();
 517       $this->_values[$name][] = array_keys($value);
 518     }
 519     $this->_values[$name][] = array_values($value);
 520   }
 521
 522   /**
 523    * @param string $daoName
 524    * @param bool $onlyKeys
 525    *
 526    * @return array
 527    */
 528   public function dbFields($daoName, $onlyKeys = FALSE) {
 529     static $_fieldsRetrieved = array();
 530
 531     if (!isset($_fieldsRetrieved[$daoName])) {
 532       $_fieldsRetrieved[$daoName] = array();
 533       $daoFile = str_replace('_',
 534           DIRECTORY_SEPARATOR,
 535           $daoName
 536         ) . '.php';
 537       include_once $daoFile;
 538
 539       $daoFields = &$daoName::fields();
 540
 541       foreach ($daoFields as $key => & $value) {
 542         $_fieldsRetrieved[$daoName][$value['name']] = array(
 543           'uniqueName' => $key,
 544           'type' => $value['type'],
 545           'title' => CRM_Utils_Array::value('title', $value, NULL),
 546         );
 547       }
 548     }
 549
 550     if ($onlyKeys) {
 551       return array_keys($_fieldsRetrieved[$daoName]);
 552     }
 553     else {
 554       return $_fieldsRetrieved[$daoName];
 555     }
 556   }
 557
 558   /**
 559    * @param $contactIDs
 560    * @param $additionalContacts
 561    */
 562   public function addAdditionalContacts($contactIDs, &$additionalContacts) {
 563     if (!$this->_discoverContacts) {
 564       return;
 565     }
 566
 567     foreach ($contactIDs as $cid) {
 568       if ($cid &&
 569         !isset($this->_allContactIDs[$cid]) &&
 570         !isset($additionalContacts[$cid])
 571       ) {
 572         $additionalContacts[$cid] = $cid;
 573       }
 574     }
 575   }
 576
 577   /**
 578    * @param $contactIDs
 579    */
 580   public function export(&$contactIDs) {
 581     $chunks = &$this->splitContactIDs($contactIDs);
 582
 583     $additionalContactIDs = array();
 584
 585     foreach ($chunks as $chunk) {
 586       $this->getValues($chunk, $additionalContactIDs);
 587     }
 588
 589     if (!empty($additionalContactIDs)) {
 590       $this->_allContactIDs = $this->_allContactIDs + $additionalContactIDs;
 591       $this->export($additionalContactIDs);
 592     }
 593   }
 594
 595   /**
 596    * @param string $fileName
 597    * @param null $lastExportTime
 598    * @param bool $discoverContacts
 599    */
 600   public function run(
 601     $fileName,
 602     $lastExportTime = NULL,
 603     $discoverContacts = FALSE
 604   ) {
 605     $this->_discoverContacts = $discoverContacts;
 606
 607     if (!$lastExportTime) {
 608       $sql = "
 609 SELECT id
 610 FROM   civicrm_contact
 611 ";
 612     }
 613     else {
 614       $sql = "(
 615 SELECT DISTINCT entity_id
 616 FROM   civicrm_log
 617 WHERE  entity_table = 'civicrm_contact'
 618 AND    modified_date >= $lastExportTime
 619 ) UNION (
 620 SELECT DISTINCT contact_id
 621 FROM   civicrm_subscription_history
 622 WHERE  date >= $lastExportTime
 623 )
 624 ";
 625     }
 626
 627     $dao = &CRM_Core_DAO::executeQuery($sql);
 628
 629     $contactIDs = array();
 630     while ($dao->fetch()) {
 631       $contactIDs[$dao->id] = $dao->id;
 632     }
 633
 634     $this->_allContactIDs = $contactIDs;
 635     $this->_values = array();
 636
 637     $this->metaData();
 638
 639     $this->export($contactIDs);
 640
 641     $json = json_encode($this->_values, JSON_NUMERIC_CHECK);
 642     file_put_contents($fileName,
 643       $json
 644     );
 645
 646     // print_r( json_decode( $json ) );
 647   }
 648
 649 }