CRM/Utils/Migrate/ExportJSON.php

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