CRM/Utils/Migrate/Export.php

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