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