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