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 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 new fields
 439    */
 440   public function computeMappedFields($mappedFields, $dao) {
 441     $keyValues = array();
 442     if ($mappedFields) {
 443       foreach ($mappedFields as $mappedField) {
 444         if (isset($dao->{$mappedField[1]})) {
 445           if (isset($mappedField[3])) {
 446             $label = $this->_xml[$mappedField[0]]['idNameMap']["{$mappedField[3]}." . $dao->{$mappedField[1]}];
 447           }
 448           else {
 449             $label = $this->_xml[$mappedField[0]]['idNameMap'][$dao->{$mappedField[1]}];
 450           }
 451           $keyValues[$mappedField[2]] = $label;
 452         }
 453       }
 454     }
 455     return $keyValues;
 456   }
 457
 458   /**
 459    * @param string $objectName
 460    *   Business-entity/xml-tag name.
 461    * @param CRM_Core_DAO $object
 462    * @param $mappedFields
 463    *
 464    * @return array
 465    */
 466   public function exportDAO($objectName, $object, $mappedFields) {
 467     $dbFields = & $object->fields();
 468
 469     // Filter the list of keys and values so that we only export interesting stuff
 470     $keyValues = array();
 471     foreach ($dbFields as $name => $dontCare) {
 472       // ignore all ids
 473       if ($name == 'id' || substr($name, -3, 3) == '_id') {
 474         continue;
 475       }
 476       if (isset($object->$name) && $object->$name !== NULL) {
 477         // hack for extends_entity_column_value
 478         if ($name == 'extends_entity_column_value') {
 479           if (in_array($object->extends, array('Event', 'Activity', 'Relationship', 'Individual', 'Organization', 'Household', 'Case'))) {
 480             if ($object->extends == 'Event') {
 481               $key = 'event_type';
 482             }
 483             elseif ($object->extends == 'Activity') {
 484               $key = 'activity_type';
 485             }
 486             elseif ($object->extends == 'Relationship') {
 487               $key = 'relationship_type';
 488             }
 489             elseif($object->extends == 'Case') {
 490               $key = 'case_type';
 491             }
 492             $types = explode(CRM_Core_DAO::VALUE_SEPARATOR, substr($object->$name, 1, -1));
 493             $values = array();
 494             if (in_array($object->extends, array('Individual', 'Organization', 'Household'))) {
 495               $key = 'contact_type';
 496               $values = $types;
 497             }
 498             else {
 499               foreach ($types as $type) {
 500                 if (in_array($key, array('activity_type', 'event_type', 'case_type'))) {
 501                   $ogID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', $key, 'id', 'name');
 502                   $ovParams = array('option_group_id' => $ogID, 'value' => $type);
 503                   CRM_Core_BAO_OptionValue::retrieve($ovParams, $oValue);
 504                   $values[] = $oValue['name'];
 505                 }
 506                 else {
 507                   $relTypeName = CRM_Core_DAO::getFieldValue('CRM_Contact_BAO_RelationshipType', $type, 'name_a_b', 'id');
 508                   $values[] = $relTypeName;
 509                 }
 510               }
 511             }
 512             $keyValues['extends_entity_column_value_option_group'] = $key;
 513             $value = implode(',', $values);
 514             $object->extends_entity_column_value = $value;
 515           }
 516           else {
 517             echo "This extension: {$object->extends} is not yet handled";
 518             exit();
 519           }
 520         }
 521
 522         $value = $object->$name;
 523         if ($name == 'field_name') {
 524           // hack for profile field_name
 525           if (substr($value, 0, 7) == 'custom_') {
 526             $cfID = substr($value, 7);
 527             list($tableName, $columnName, $groupID) = CRM_Core_BAO_CustomField::getTableColumnGroup($cfID);
 528             $value = "custom.{$tableName}.{$columnName}";
 529           }
 530         }
 531         $keyValues[$name] = $value;
 532       }
 533     }
 534
 535     $keyValues += $this->computeMappedFields($mappedFields, $object);
 536
 537     return $keyValues;
 538   }
 539
 540   /**
 541    * @param string $tagName
 542    * @param array $keyValues
 543    * @throws Exception
 544    * @return string XML
 545    */
 546   public function renderKeyValueXML($tagName, $keyValues) {
 547     $xml = "    <$tagName>";
 548     foreach ($keyValues as $k => $v) {
 549       $xml .= "\n      " . $this->renderTextTag($k, str_replace(CRM_Core_DAO::VALUE_SEPARATOR, self::XML_VALUE_SEPARATOR, $v));
 550     }
 551     $xml .= "\n    </$tagName>\n";
 552     return $xml;
 553   }
 554
 555   /**
 556    * @param string $name
 557    *   Tag name.
 558    * @param string $value
 559    *   Text.
 560    * @param string $prefix
 561    *
 562    * @throws Exception
 563    * @return string XML
 564    */
 565   public function renderTextTag($name, $value, $prefix = '') {
 566     if (!preg_match('/^[a-zA-Z0-9\_]+$/', $name)) {
 567       throw new Exception("Malformed tag name: $name");
 568     }
 569     return $prefix . "<$name>" . htmlentities($value) . "</$name>";
 570   }
 571 }