CRM/Utils/Migrate/Export.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.5                                                |
   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   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   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 list of custom groups to export
 271    * @return void
 272    */
 273   function buildCustomGroups($customGroupIds) {
 274     $customGroupIdsSql = implode(',', array_filter($customGroupIds, 'is_numeric'));
 275     if (empty($customGroupIdsSql)) {
 276       return;
 277     }
 278
 279     $sql = "
 280       SELECT distinct(g.id), g.*
 281       FROM   civicrm_option_group g,
 282              civicrm_custom_field f,
 283              civicrm_custom_group cg
 284       WHERE  f.option_group_id = g.id
 285       AND    f.custom_group_id = cg.id
 286       AND    cg.id in ($customGroupIdsSql)
 287     ";
 288     $this->fetch('optionGroup', 'CRM_Core_DAO_OptionGroup', $sql);
 289
 290     $sql = "
 291       SELECT distinct(v.id), v.*, g.name as prefix
 292       FROM   civicrm_option_value v,
 293              civicrm_option_group g,
 294              civicrm_custom_field f,
 295              civicrm_custom_group cg
 296       WHERE  v.option_group_id = g.id
 297       AND    f.option_group_id = g.id
 298       AND    f.custom_group_id = cg.id
 299       AND    cg.id in ($customGroupIdsSql)
 300     ";
 301
 302     $this->fetch('optionValue', 'CRM_Core_DAO_OptionValue', $sql);
 303
 304     $sql = "
 305       SELECT cg.*
 306       FROM   civicrm_custom_group cg
 307       WHERE  cg.id in ($customGroupIdsSql)
 308
 309     ";
 310     $this->fetch('customGroup', 'CRM_Core_DAO_CustomGroup', $sql);
 311
 312     $sql = "
 313       SELECT f.*
 314       FROM   civicrm_custom_field f,
 315              civicrm_custom_group cg
 316       WHERE  f.custom_group_id = cg.id
 317       AND    cg.id in ($customGroupIdsSql)
 318     ";
 319     $this->fetch('customField', 'CRM_Core_DAO_CustomField', $sql);
 320   }
 321
 322   /**
 323    * @param array $ufGroupIds list of custom groups to export
 324    * @return void
 325    */
 326   function buildUFGroups($ufGroupIds) {
 327     $ufGroupIdsSql = implode(',', array_filter($ufGroupIds, 'is_numeric'));
 328     if (empty($ufGroupIdsSql)) {
 329       return;
 330     }
 331
 332     $sql = "
 333       SELECT cg.*
 334       FROM   civicrm_uf_group cg
 335       WHERE  cg.id IN ($ufGroupIdsSql)
 336
 337     ";
 338     $this->fetch('profileGroup', 'CRM_Core_DAO_UFGroup', $sql);
 339
 340     $sql = "
 341       SELECT f.*
 342       FROM   civicrm_uf_field f,
 343              civicrm_uf_group cg
 344       WHERE  f.uf_group_id = cg.id
 345       AND    cg.id IN ($ufGroupIdsSql)
 346     ";
 347     $this->fetch('profileField', 'CRM_Core_DAO_UFField', $sql);
 348
 349     $sql = "
 350       SELECT *
 351       FROM   civicrm_uf_join
 352       WHERE  entity_table IS NULL
 353       AND    entity_id    IS NULL
 354       AND    uf_group_id  IN ($ufGroupIdsSql)
 355     ";
 356     $this->fetch('profileJoin', 'CRM_Core_DAO_UFJoin', $sql);
 357   }
 358
 359   /**
 360    * Render the in-memory representation as XML
 361    *
 362    * @return string XML
 363    */
 364   function toXML() {
 365     $buffer = '<?xml version="1.0" encoding="iso-8859-1" ?>';
 366     $buffer .= "\n\n<CustomData>\n";
 367     foreach (array_keys($this->_xml) as $key) {
 368       if (!empty($this->_xml[$key]['data'])) {
 369         $buffer .= "  <{$this->_xml[$key]['scope']}>\n";
 370         foreach ($this->_xml[$key]['data'] as $item) {
 371           $buffer .= $this->renderKeyValueXML($this->_xml[$key]['name'], $item);
 372         }
 373         $buffer .= "  </{$this->_xml[$key]['scope']}>\n";
 374       }
 375       elseif ($this->_xml[$key]['required']) {
 376         CRM_Core_Error::fatal("No records in DB for $key");
 377       }
 378     }
 379     $buffer .= "</CustomData>\n";
 380     return $buffer;
 381   }
 382
 383   /**
 384    * Generate an array-tree representation of the exported elements.
 385    *
 386    * @return array
 387    */
 388   function toArray() {
 389     $result = array();
 390     foreach (array_keys($this->_xml) as $key) {
 391       if (!empty($this->_xml[$key]['data'])) {
 392         $result[ $this->_xml[$key]['name'] ] = array_values($this->_xml[$key]['data']);
 393       }
 394     }
 395     return $result;
 396   }
 397
 398   /**
 399    * @param string $groupName
 400    * @param string $daoName
 401    * @param null $sql
 402    */
 403   function fetch($groupName, $daoName, $sql = NULL) {
 404     $idNameFields = isset($this->_xml[$groupName]['idNameFields']) ? $this->_xml[$groupName]['idNameFields'] : NULL;
 405     $mappedFields = isset($this->_xml[$groupName]['mappedFields']) ? $this->_xml[$groupName]['mappedFields'] : NULL;
 406
 407     $dao = new $daoName();
 408     if ($sql) {
 409       $dao->query($sql);
 410     }
 411     else {
 412       $dao->find();
 413     }
 414
 415     while ($dao->fetch()) {
 416       $this->_xml[$groupName]['data'][$dao->id] = $this->exportDAO($this->_xml[$groupName]['name'], $dao, $mappedFields);
 417       if ($idNameFields) {
 418         // index the id/name fields so that we can translate from FK ids to FK names
 419         if (isset($idNameFields[2])) {
 420           $this->_xml[$groupName]['idNameMap'][$dao->{$idNameFields[2]} . '.' . $dao->{$idNameFields[0]}] = $dao->{$idNameFields[1]};
 421         }
 422         else {
 423           $this->_xml[$groupName]['idNameMap'][$dao->{$idNameFields[0]}] = $dao->{$idNameFields[1]};
 424         }
 425       }
 426     }
 427   }
 428
 429   /**
 430    * Compute any fields of the entity defined by the $mappedFields specification
 431    *
 432    * @param array $mappedFields each item is an array(0 => MappedEntityname, 1 => InputFieldName (id-field), 2 => OutputFieldName (name-field), 3 => OptionalPrefix)
 433    * @param CRM_Core_DAO $dao the entity for which we want to prepare mapped fields
 434    * @return array new fields
 435    */
 436   public function computeMappedFields($mappedFields, $dao) {
 437     $keyValues = array();
 438     if ($mappedFields) {
 439       foreach ($mappedFields as $mappedField) {
 440         if (isset($dao->{$mappedField[1]})) {
 441           if (isset($mappedField[3])) {
 442             $label = $this->_xml[$mappedField[0]]['idNameMap']["{$mappedField[3]}." . $dao->{$mappedField[1]}];
 443           }
 444           else {
 445             $label = $this->_xml[$mappedField[0]]['idNameMap'][$dao->{$mappedField[1]}];
 446           }
 447           $keyValues[$mappedField[2]] = $label;
 448         }
 449       }
 450     }
 451     return $keyValues;
 452   }
 453
 454   /**
 455    * @param string $objectName business-entity/xml-tag name
 456    * @param CRM_Core_DAO $object
 457    * @param $mappedFields
 458    *
 459    * @return array
 460    */
 461   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('Event', 'Activity', 'Relationship', 'Individual', 'Organization', 'Household', 'Case'))) {
 475             if ($object->extends == 'Event') {
 476               $key = 'event_type';
 477             }
 478             elseif ($object->extends == 'Activity') {
 479               $key = 'activity_type';
 480             }
 481             elseif ($object->extends == 'Relationship') {
 482               $key = 'relationship_type';
 483             }
 484             elseif($object->extends == 'Case') {
 485               $key = 'case_type';
 486             }
 487             $types = explode(CRM_Core_DAO::VALUE_SEPARATOR, substr($object->$name, 1, -1));
 488             $values = array();
 489             if (in_array($object->extends, array('Individual', 'Organization', 'Household'))) {
 490               $key = 'contact_type';
 491               $values = $types;
 492             }
 493             else {
 494               foreach ($types as $type) {
 495                 if (in_array($key, array('activity_type', 'event_type', 'case_type'))) {
 496                   $ogID = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionGroup', $key, 'id', 'name');
 497                   $ovParams = array('option_group_id' => $ogID, 'value' => $type);
 498                   CRM_Core_BAO_OptionValue::retrieve($ovParams, $oValue);
 499                   $values[] = $oValue['name'];
 500                 }
 501                 else {
 502                   $relTypeName = CRM_Core_DAO::getFieldValue('CRM_Contact_BAO_RelationshipType', $type, 'name_a_b', 'id');
 503                   $values[] = $relTypeName;
 504                 }
 505               }
 506             }
 507             $keyValues['extends_entity_column_value_option_group'] = $key;
 508             $value = implode(',', $values);
 509             $object->extends_entity_column_value = $value;
 510           }
 511           else {
 512             echo "This extension: {$object->extends} is not yet handled";
 513             exit();
 514           }
 515         }
 516
 517         $value = $object->$name;
 518         if ($name == 'field_name') {
 519           // hack for profile field_name
 520           if (substr($value, 0, 7) == 'custom_') {
 521             $cfID = substr($value, 7);
 522             list($tableName, $columnName, $groupID) = CRM_Core_BAO_CustomField::getTableColumnGroup($cfID);
 523             $value = "custom.{$tableName}.{$columnName}";
 524           }
 525         }
 526         $keyValues[$name] = $value;
 527       }
 528     }
 529
 530     $keyValues += $this->computeMappedFields($mappedFields, $object);
 531
 532     return $keyValues;
 533   }
 534
 535   /**
 536    * @param string $tagName
 537    * @param array $keyValues
 538    * @throws Exception
 539    * @return string XML
 540    */
 541   public function renderKeyValueXML($tagName, $keyValues) {
 542     $xml = "    <$tagName>";
 543     foreach ($keyValues as $k => $v) {
 544       $xml .= "\n      " . $this->renderTextTag($k, str_replace(CRM_Core_DAO::VALUE_SEPARATOR, self::XML_VALUE_SEPARATOR, $v));
 545     }
 546     $xml .= "\n    </$tagName>\n";
 547     return $xml;
 548   }
 549
 550   /**
 551    * @param string $name tag name
 552    * @param string $value text
 553    * @param string $prefix
 554    *
 555    * @throws Exception
 556    * @return string XML
 557    */
 558   function renderTextTag($name, $value, $prefix = '') {
 559     if (!preg_match('/^[a-zA-Z0-9\_]+$/', $name)) {
 560       throw new Exception("Malformed tag name: $name");
 561     }
 562     return $prefix . "<$name>" . htmlentities($value) . "</$name>";
 563   }
 564 }
 565