CRM/Utils/Migrate/ExportJSON.php

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