CRM/Utils/Migrate/Export.php

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