CRM/Utils/Migrate/Export.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Utils_Migrate_Export {
  36
  37   const XML_VALUE_SEPARATOR = ":;:;:;";
  38
  39   /**
  40    * @var array description of export field mapping
  41    *
  42    * @code
  43    * 'exampleEntityMappingName' => array(
  44    *   'data' => array(),                     // placeholder; this will get filled-in during execution
  45    *   'name' => 'CustomGroup',               // per-item XML tag name
  46    *   'scope' => 'CustomGroups',             // container XML tag name
  47    *   'required' => FALSE,                   // whether we *must* find records of this type
  48    *   'idNameFields' => array('id', 'name'), // name of the (local/autogenerated) "id" and (portable) "name" columns
  49    *   'idNameMap' => array(),                // placeholder; this will get filled-in during execution
  50    * ),
  51    * @endcode
  52    */
  53   protected $_xml;
  54
  55   /**
  56    */
  57   public function __construct() {
  58     $this->_xml = array(
  59       'customGroup' => array(
  60         'data' => array(),
  61         'name' => 'CustomGroup',
  62         'scope' => 'CustomGroups',
  63         'required' => FALSE,
  64         'idNameFields' => array('id', 'name'),
  65         'idNameMap' => array(),
  66       ),
  67       'customField' => array(
  68         'data' => array(),
  69         'name' => 'CustomField',
  70         'scope' => 'CustomFields',
  71         'required' => FALSE,
  72         'idNameFields' => array('id', 'column_name'),
  73         'idNameMap' => array(),
  74         'mappedFields' => array(
  75           array('optionGroup', 'option_group_id', 'option_group_name'),
  76           array('customGroup', 'custom_group_id', 'custom_group_name'),
  77         ),
  78       ),
  79       'optionGroup' => array(
  80         'data' => array(),
  81         'name' => 'OptionGroup',
  82         'scope' => 'OptionGroups',
  83         'required' => FALSE,
  84         'idNameMap' => array(),
  85         'idNameFields' => array('id', 'name'),
  86       ),
  87       'relationshipType' => array(
  88         'data' => array(),
  89         'name' => 'RelationshipType',
  90         'scope' => 'RelationshipTypes',
  91         'required' => FALSE,
  92         'idNameFields' => array('id', 'name_a_b'),
  93         'idNameMap' => array(),
  94       ),
  95       'locationType' => array(
  96         'data' => array(),
  97         'name' => 'LocationType',
  98         'scope' => 'LocationTypes',
  99         'required' => FALSE,
 100         'idNameFields' => array('id', 'name'),
 101         'idNameMap' => array(),
 102       ),
 103       'optionValue' => array(
 104         'data' => array(),
 105         'name' => 'OptionValue',
 106         'scope' => 'OptionValues',
 107         'required' => FALSE,
 108         'idNameMap' => array(),
 109         'idNameFields' => array('value', 'name', 'prefix'),
 110         'mappedFields' => array(
 111           array('optionGroup', 'option_group_id', 'option_group_name'),
 112         ),
 113       ),
 114       'profileGroup' => array(
 115         'data' => array(),
 116         'name' => 'ProfileGroup',
 117         'scope' => 'ProfileGroups',
 118         'required' => FALSE,
 119         'idNameFields' => array('id', 'title'),
 120         'idNameMap' => array(),
 121       ),
 122       'profileField' => array(
 123         'data' => array(),
 124         'name' => 'ProfileField',
 125         'scope' => 'ProfileFields',
 126         'required' => FALSE,
 127         'idNameMap' => array(),
 128         'mappedFields' => array(
 129           array('profileGroup', 'uf_group_id', 'profile_group_name'),
 130         ),
 131       ),
 132       'profileJoin' => array(
 133         'data' => array(),
 134         'name' => 'ProfileJoin',
 135         'scope' => 'ProfileJoins',
 136         'required' => FALSE,
 137         'idNameMap' => array(),
 138         'mappedFields' => array(
 139           array('profileGroup', 'uf_group_id', 'profile_group_name'),
 140         ),
 141       ),
 142       'mappingGroup' => array(
 143         'data' => array(),
 144         'name' => 'MappingGroup',
 145         'scope' => 'MappingGroups',
 146         'required' => FALSE,
 147         'idNameFields' => array('id', 'name'),
 148         'idNameMap' => array(),
 149         'mappedFields' => array(
 150           array('optionValue', 'mapping_type_id', 'mapping_type_name', 'mapping_type'),
 151         ),
 152       ),
 153       'mappingField' => array(
 154         'data' => array(),
 155         'name' => 'MappingField',
 156         'scope' => 'MappingFields',
 157         'required' => FALSE,
 158         'idNameMap' => array(),
 159         'mappedFields' => array(
 160           array('mappingGroup', 'mapping_id', 'mapping_group_name'),
 161           array('locationType', 'location_type_id', 'location_type_name'),
 162           array('relationshipType', 'relationship_type_id', 'relationship_type_name'),
 163         ),
 164       ),
 165     );
 166   }
 167
 168   /**
 169    * Scan local customizations and build an in-memory representation
 170    *
 171    * @return void
 172    */
 173   public function build() {
 174     // fetch the option group / values for
 175     // activity type and event_type
 176
 177     $optionGroups = "( 'activity_type', 'event_type', 'mapping_type' )";
 178
 179     $sql = "
 180       SELECT distinct(g.id), g.*
 181       FROM   civicrm_option_group g
 182       WHERE  g.name IN $optionGroups
 183     ";
 184     $this->fetch('optionGroup', 'CRM_Core_DAO_OptionGroup', $sql);
 185
 186     $sql = "
 187       SELECT distinct(g.id), g.*
 188       FROM   civicrm_option_group g,
 189              civicrm_custom_field f,
 190              civicrm_custom_group cg
 191       WHERE  f.option_group_id = g.id
 192       AND    f.custom_group_id = cg.id
 193       AND    cg.is_active = 1
 194     ";
 195     $this->fetch('optionGroup', 'CRM_Core_DAO_OptionGroup', $sql);
 196
 197     $sql = "
 198       SELECT v.*, g.name as prefix
 199       FROM   civicrm_option_value v,
 200              civicrm_option_group g
 201       WHERE  v.option_group_id = g.id
 202       AND    g.name IN $optionGroups
 203     ";
 204
 205     $this->fetch('optionValue', 'CRM_Core_DAO_OptionValue', $sql);
 206
 207     $sql = "
 208       SELECT distinct(v.id), v.*, g.name as prefix
 209       FROM   civicrm_option_value v,
 210              civicrm_option_group g,
 211              civicrm_custom_field f,
 212              civicrm_custom_group cg
 213       WHERE  v.option_group_id = g.id
 214       AND    f.option_group_id = g.id
 215       AND    f.custom_group_id = cg.id
 216       AND    cg.is_active = 1
 217     ";
 218
 219     $this->fetch('optionValue', 'CRM_Core_DAO_OptionValue', $sql);
 220
 221     $sql = "
 222       SELECT rt.*
 223       FROM   civicrm_relationship_type rt
 224       WHERE  rt.is_active = 1
 225     ";
 226     $this->fetch('relationshipType', 'CRM_Contact_DAO_RelationshipType', $sql);
 227
 228     $sql = "
 229       SELECT lt.*
 230       FROM   civicrm_location_type lt
 231       WHERE  lt.is_active = 1
 232     ";
 233     $this->fetch('locationType', 'CRM_Core_DAO_LocationType', $sql);
 234
 235     $sql = "
 236       SELECT cg.*
 237       FROM   civicrm_custom_group cg
 238       WHERE  cg.is_active = 1
 239     ";
 240     $this->fetch('customGroup', 'CRM_Core_DAO_CustomGroup', $sql);
 241
 242     $sql = "
 243       SELECT f.*
 244       FROM   civicrm_custom_field f,
 245              civicrm_custom_group cg
 246       WHERE  f.custom_group_id = cg.id
 247       AND    cg.is_active = 1
 248     ";
 249     $this->fetch('customField', 'CRM_Core_DAO_CustomField', $sql);
 250
 251     $this->fetch('profileGroup', 'CRM_Core_DAO_UFGroup');
 252
 253     $this->fetch('profileField', 'CRM_Core_DAO_UFField');
 254
 255     $sql = "
 256       SELECT *
 257       FROM   civicrm_uf_join
 258       WHERE  entity_table IS NULL
 259       AND    entity_id    IS NULL
 260     ";
 261     $this->fetch('profileJoin', 'CRM_Core_DAO_UFJoin', $sql);
 262
 263     $this->fetch('mappingGroup', 'CRM_Core_DAO_Mapping');
 264
 265     $this->fetch('mappingField', 'CRM_Core_DAO_MappingField');
 266   }
 267
 268   /**
 269    * @param array $customGroupIds
 270    *   List of custom groups to export.
 271    * @return void
 272    */
 273   public function buildCustomGroups($customGroupIds) {
 274     $customGroupIdsSql = implode(',', array_filter($customGroupIds, 'is_numeric'));
 275     if (empty($customGroupIdsSql)) {
 276       return;
 277     }
 278
 279     $sql = "
 280       SELECT distinct(g.id), g.*
 281       FROM   civicrm_option_group g,
 282              civicrm_custom_field f,
 283              civicrm_custom_group cg
 284       WHERE  f.option_group_id = g.id
 285       AND    f.custom_group_id = cg.id
 286       AND    cg.id in ($customGroupIdsSql)
 287     ";
 288     $this->fetch('optionGroup', 'CRM_Core_DAO_OptionGroup', $sql);
 289
 290     $sql = "
 291       SELECT distinct(v.id), v.*, g.name as prefix
 292       FROM   civicrm_option_value v,
 293              civicrm_option_group g,
 294              civicrm_custom_field f,
 295              civicrm_custom_group cg
 296       WHERE  v.option_group_id = g.id
 297       AND    f.option_group_id = g.id
 298       AND    f.custom_group_id = cg.id
 299       AND    cg.id in ($customGroupIdsSql)
 300     ";
 301
 302     $this->fetch('optionValue', 'CRM_Core_DAO_OptionValue', $sql);
 303
 304     $sql = "
 305       SELECT cg.*
 306       FROM   civicrm_custom_group cg
 307       WHERE  cg.id in ($customGroupIdsSql)
 308
 309     ";
 310     $this->fetch('customGroup', 'CRM_Core_DAO_CustomGroup', $sql);
 311
 312     $sql = "
 313       SELECT f.*
 314       FROM   civicrm_custom_field f,
 315              civicrm_custom_group cg
 316       WHERE  f.custom_group_id = cg.id
 317       AND    cg.id in ($customGroupIdsSql)
 318     ";
 319     $this->fetch('customField', 'CRM_Core_DAO_CustomField', $sql);
 320   }
 321
 322   /**
 323    * @param array $ufGroupIds
 324    *   List of custom groups to export.
 325    * @return void
 326    */
 327   public function buildUFGroups($ufGroupIds) {
 328     $ufGroupIdsSql = implode(',', array_filter($ufGroupIds, 'is_numeric'));
 329     if (empty($ufGroupIdsSql)) {
 330       return;
 331     }
 332
 333     $sql = "
 334       SELECT cg.*
 335       FROM   civicrm_uf_group cg
 336       WHERE  cg.id IN ($ufGroupIdsSql)
 337
 338     ";
 339     $this->fetch('profileGroup', 'CRM_Core_DAO_UFGroup', $sql);
 340
 341     $sql = "
 342       SELECT f.*
 343       FROM   civicrm_uf_field f,
 344              civicrm_uf_group cg
 345       WHERE  f.uf_group_id = cg.id
 346       AND    cg.id IN ($ufGroupIdsSql)
 347     ";
 348     $this->fetch('profileField', 'CRM_Core_DAO_UFField', $sql);
 349
 350     $sql = "
 351       SELECT *
 352       FROM   civicrm_uf_join
 353       WHERE  entity_table IS NULL
 354       AND    entity_id    IS NULL
 355       AND    uf_group_id  IN ($ufGroupIdsSql)
 356     ";
 357     $this->fetch('profileJoin', 'CRM_Core_DAO_UFJoin', $sql);
 358   }
 359
 360   /**
 361    * Render the in-memory representation as XML
 362    *
 363    * @return string
 364    *   XML
 365    */
 366   public function toXML() {
 367     $buffer = '<?xml version="1.0" encoding="iso-8859-1" ?>';
 368     $buffer .= "\n\n<CustomData>\n";
 369     foreach (array_keys($this->_xml) as $key) {
 370       if (!empty($this->_xml[$key]['data'])) {
 371         $buffer .= "  <{$this->_xml[$key]['scope']}>\n";
 372         foreach ($this->_xml[$key]['data'] as $item) {
 373           $buffer .= $this->renderKeyValueXML($this->_xml[$key]['name'], $item);
 374         }
 375         $buffer .= "  </{$this->_xml[$key]['scope']}>\n";
 376       }
 377       elseif ($this->_xml[$key]['required']) {
 378         CRM_Core_Error::fatal("No records in DB for $key");
 379       }
 380     }
 381     $buffer .= "</CustomData>\n";
 382     return $buffer;
 383   }
 384
 385   /**
 386    * Generate an array-tree representation of the exported elements.
 387    *
 388    * @return array
 389    */
 390   public function toArray() {
 391     $result = array();
 392     foreach (array_keys($this->_xml) as $key) {
 393       if (!empty($this->_xml[$key]['data'])) {
 394         $result[$this->_xml[$key]['name']] = array_values($this->_xml[$key]['data']);
 395       }
 396     }
 397     return $result;
 398   }
 399
 400   /**
 401    * @param string $groupName
 402    * @param string $daoName
 403    * @param null $sql
 404    */
 405   public function fetch($groupName, $daoName, $sql = NULL) {
 406     $idNameFields = isset($this->_xml[$groupName]['idNameFields']) ? $this->_xml[$groupName]['idNameFields'] : NULL;
 407     $mappedFields = isset($this->_xml[$groupName]['mappedFields']) ? $this->_xml[$groupName]['mappedFields'] : NULL;
 408
 409     $dao = new $daoName();
 410     if ($sql) {
 411       $dao->query($sql);
 412     }
 413     else {
 414       $dao->find();
 415     }
 416
 417     while ($dao->fetch()) {
 418       $this->_xml[$groupName]['data'][$dao->id] = $this->exportDAO($this->_xml[$groupName]['name'], $dao, $mappedFields);
 419       if ($idNameFields) {
 420         // index the id/name fields so that we can translate from FK ids to FK names
 421         if (isset($idNameFields[2])) {
 422           $this->_xml[$groupName]['idNameMap'][$dao->{$idNameFields[2]} . '.' . $dao->{$idNameFields[0]}] = $dao->{$idNameFields[1]};
 423         }
 424         else {
 425           $this->_xml[$groupName]['idNameMap'][$dao->{$idNameFields[0]}] = $dao->{$idNameFields[1]};
 426         }
 427       }
 428     }
 429   }
 430
 431   /**
 432    * Compute any fields of the entity defined by the $mappedFields specification
 433    *
 434    * @param array $mappedFields
 435    *   Each item is an array(0 => MappedEntityname, 1 => InputFieldName (id-field), 2 => OutputFieldName (name-field), 3 => OptionalPrefix).
 436    * @param CRM_Core_DAO $dao
 437    *   The entity for which we want to prepare mapped fields.
 438    * @return array
 439    *   new fields
 440    */
 441   public function computeMappedFields($mappedFields, $dao) {
 442     $keyValues = array();
 443     if ($mappedFields) {
 444       foreach ($mappedFields as $mappedField) {
 445         if (isset($dao->{$mappedField[1]})) {
 446           if (isset($mappedField[3])) {
 447             $label = $this->_xml[$mappedField[0]]['idNameMap']["{$mappedField[3]}." . $dao->{$mappedField[1]}];
 448           }
 449           else {
 450             $label = $this->_xml[$mappedField[0]]['idNameMap'][$dao->{$mappedField[1]}];
 451           }
 452           $keyValues[$mappedField[2]] = $label;
 453         }
 454       }
 455     }
 456     return $keyValues;
 457   }
 458
 459   /**
 460    * @param string $objectName
 461    *   Business-entity/xml-tag name.
 462    * @param CRM_Core_DAO $object
 463    * @param $mappedFields
 464    *
 465    * @return array
 466    */
 467   public function exportDAO($objectName, $object, $mappedFields) {
 468     $dbFields = &$object->fields();
 469
 470     // Filter the list of keys and values so that we only export interesting stuff
 471     $keyValues = array();
 472     foreach ($dbFields as $name => $dontCare) {
 473       // ignore all ids
 474       if ($name == 'id' || substr($name, -3, 3) == '_id') {
 475         continue;
 476       }
 477       if (isset($object->$name) && $object->$name !== NULL) {
 478         // hack for extends_entity_column_value
 479         if ($name == 'extends_entity_column_value') {
 480           if (in_array($object->extends, array(
 481               'Event',
 482               'Activity',
 483               'Relationship',
 484               'Individual',
 485               'Organization',
 486               'Household',
 487               'Case',
 488             ))) {
 489             if ($object->extends == 'Event') {
 490               $key = 'event_type';
 491             }
 492             elseif ($object->extends == 'Activity') {
 493               $key = 'activity_type';
 494             }
 495             elseif ($object->extends == 'Relationship') {
 496               $key = 'relationship_type';
 497             }
 498             elseif ($object->extends == 'Case') {
 499               $key = 'case_type';
 500             }
 501             $types = explode(CRM_Core_DAO::VALUE_SEPARATOR, substr($object->$name, 1, -1));
 502             $values = array();
 503             if (in_array($object->extends, array('Individual', 'Organization', 'Household'))) {
 504               $key = 'contact_type';
 505               $values = $types;
 506             }
 507             else {
 508               foreach ($types as $type) {
 509                 if (in_array($key, array('activity_type', 'event_type', 'case_type'))) {
 510                   $ogID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', $key, 'id', 'name');
 511                   $ovParams = array('option_group_id' => $ogID, 'value' => $type);
 512                   CRM_Core_BAO_OptionValue::retrieve($ovParams, $oValue);
 513                   $values[] = $oValue['name'];
 514                 }
 515                 else {
 516                   $relTypeName = CRM_Core_DAO::getFieldValue('CRM_Contact_BAO_RelationshipType', $type, 'name_a_b', 'id');
 517                   $values[] = $relTypeName;
 518                 }
 519               }
 520             }
 521             $keyValues['extends_entity_column_value_option_group'] = $key;
 522             $value = implode(',', $values);
 523             $object->extends_entity_column_value = $value;
 524           }
 525           else {
 526             echo "This extension: {$object->extends} is not yet handled";
 527             exit();
 528           }
 529         }
 530
 531         $value = $object->$name;
 532         if ($name == 'field_name') {
 533           // hack for profile field_name
 534           if (substr($value, 0, 7) == 'custom_') {
 535             $cfID = substr($value, 7);
 536             list($tableName, $columnName, $groupID) = CRM_Core_BAO_CustomField::getTableColumnGroup($cfID);
 537             $value = "custom.{$tableName}.{$columnName}";
 538           }
 539         }
 540         $keyValues[$name] = $value;
 541       }
 542     }
 543
 544     $keyValues += $this->computeMappedFields($mappedFields, $object);
 545
 546     return $keyValues;
 547   }
 548
 549   /**
 550    * @param string $tagName
 551    * @param array $keyValues
 552    * @throws Exception
 553    * @return string
 554    *   XML
 555    */
 556   public function renderKeyValueXML($tagName, $keyValues) {
 557     $xml = "    <$tagName>";
 558     foreach ($keyValues as $k => $v) {
 559       $xml .= "\n      " . $this->renderTextTag($k, str_replace(CRM_Core_DAO::VALUE_SEPARATOR, self::XML_VALUE_SEPARATOR, $v));
 560     }
 561     $xml .= "\n    </$tagName>\n";
 562     return $xml;
 563   }
 564
 565   /**
 566    * @param string $name
 567    *   Tag name.
 568    * @param string $value
 569    *   Text.
 570    * @param string $prefix
 571    *
 572    * @throws Exception
 573    * @return string
 574    *   XML
 575    */
 576   public function renderTextTag($name, $value, $prefix = '') {
 577     if (!preg_match('/^[a-zA-Z0-9\_]+$/', $name)) {
 578       throw new Exception("Malformed tag name: $name");
 579     }
 580     return $prefix . "<$name>" . htmlentities($value) . "</$name>";
 581   }
 582
 583 }