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