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