tools/scripts/solr/createSyncJSON.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This code 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  * Create a xml file for a set of contact ID's in a format digestible
  14  * by our Sync scripts
  15  */
  16
  17 require_once '../../../civicrm.settings.php';
  18 require_once 'CRM/Core/Config.php';
  19
  20 define('CHUNK_SIZE', 128);
  21
  22 /**
  23  * Split a large array of contactIDs into more manageable smaller chunks
  24  * @param $contactIDs
  25  * @return array
  26  */
  27 function &splitContactIDs(&$contactIDs) {
  28   // contactIDs could be a real large array, so we split it up into
  29   // smaller chunks and then general xml for each chunk
  30   $chunks = [];
  31   $current = 0;
  32   $chunks[$current] = [];
  33   $count = 0;
  34
  35   foreach ($contactIDs as $k => $v) {
  36     $chunks[$current][$k] = $v;
  37     $count++;
  38
  39     if ($count == CHUNK_SIZE) {
  40       $current++;
  41       $chunks[$current] = [];
  42       $count = 0;
  43     }
  44   }
  45
  46   if (empty($chunks[$current])) {
  47     unset($chunks[$current]);
  48   }
  49
  50   return $chunks;
  51 }
  52
  53 /**
  54  * Given a set of contact IDs get the values
  55  * @param $contactIDs
  56  * @param $values
  57  * @param $allContactIDs
  58  * @param $addditionalContactIDs
  59  * @return array
  60  */
  61 function getValues(&$contactIDs, &$values, &$allContactIDs, &$addditionalContactIDs) {
  62   $values = [];
  63
  64   getContactInfo($contactIDs, $values);
  65   getAddressInfo($contactIDs, $values);
  66   getPhoneInfo($contactIDs, $values);
  67   getEmailInfo($contactIDs, $values);
  68   getNoteInfo($contactIDs, $values);
  69
  70   getRelationshipInfo($contactIDs, $values, $allContactIDs, $addditionalContactIDs);
  71
  72   getActivityInfo($contactIDs, $values, $allContactIDs, $addditionalContactIDs);
  73
  74   // got to do groups, tags
  75
  76   // got to do meta data
  77
  78   return $values;
  79 }
  80
  81 /**
  82  * @param $contactIDs
  83  * @param $values
  84  * @param $tableName
  85  * @param $fields
  86  * @param $whereField
  87  * @param null $additionalWhereCond
  88  * @param bool $flat
  89  */
  90 function getTableInfo(&$contactIDs, &$values, $tableName, &$fields,
  91                       $whereField, $additionalWhereCond = NULL,
  92                       $flat = FALSE
  93 ) {
  94   $selectString = implode(',', array_keys($fields));
  95   $idString = implode(',', $contactIDs);
  96
  97   $sql = "
  98 SELECT $selectString, $whereField as contact_id
  99   FROM $tableName
 100  WHERE $whereField IN ( $idString )
 101 ";
 102
 103   if ($additionalWhereCond) {
 104     $sql .= " AND $additionalWhereCond";
 105   }
 106
 107   $dao = &CRM_Core_DAO::executeQuery($sql);
 108   while ($dao->fetch()) {
 109     $contact = [];
 110     foreach ($fields as $fld => $name) {
 111       $name = $name ? $name : $fld;
 112       if (empty($dao->$fld)) {
 113         $contact[$name] = NULL;
 114       }
 115       else {
 116         $contact[$name] = $dao->$fld;
 117       }
 118     }
 119     appendValue($values, $dao->contact_id, 'contact', $contact, $flat);
 120   }
 121 }
 122
 123 /**
 124  * @param $contactIDs
 125  * @param $values
 126  */
 127 function getContactInfo(&$contactIDs, &$values) {
 128   $fields = [
 129     'id' => NULL,
 130     'sort_name' => NULL,
 131     'display_name' => NULL,
 132     'contact_type' => NULL,
 133     'legal_identifier' => NULL,
 134     'external_identifier' => NULL,
 135     'first_name' => NULL,
 136     'last_name' => NULL,
 137     'middle_name' => NULL,
 138     'household_name' => NULL,
 139     'organization_name' => NULL,
 140     'legal_name' => NULL,
 141     'job_title' => NULL,
 142   ];
 143   getTableInfo($contactIDs, $values, 'civicrm_contact', $fields, 'id', NULL, TRUE);
 144 }
 145
 146 /**
 147  * @param $contactIDs
 148  * @param $values
 149  */
 150 function getNoteInfo(&$contactIDs, &$values) {
 151   $ids = implode(',', $contactIDs);
 152
 153   $sql = "
 154 SELECT
 155       id,
 156       entity_id as contact_id,
 157       note as note, subject as subject
 158 FROM  civicrm_note
 159 WHERE entity_id IN ( $ids )
 160 AND   entity_table = 'civicrm_contact'
 161 ";
 162
 163   $dao = &CRM_Core_DAO::executeQuery($sql);
 164   while ($dao->fetch()) {
 165     $note = [
 166       'id' => $dao->id,
 167       'contact_id' => $dao->contact_id,
 168       'subject' => empty($dao->subject) ? NULL : $dao->subject,
 169       'note' => empty($dao->note) ? NULL : $dao->note,
 170     ];
 171
 172     appendValue($values, $dao->id, 'note', $note);
 173   }
 174 }
 175
 176 /**
 177  * @param $contactIDs
 178  * @param $values
 179  */
 180 function getPhoneInfo(&$contactIDs, &$values) {
 181   $ids = implode(',', $contactIDs);
 182
 183   $sql = "
 184 SELECT
 185   p.id as id,
 186   c.id as contact_id,
 187   l.name as location_type,
 188   p.phone as phone,
 189   v.label as phone_type
 190 FROM      civicrm_contact c
 191 INNER JOIN civicrm_phone          p  ON p.contact_id        = c.id
 192 LEFT  JOIN civicrm_location_type  l  ON p.location_type_id  = l.id
 193 LEFT  JOIN civicrm_option_group   g  ON g.name = 'phone_type'
 194 LEFT  JOIN civicrm_option_value   v  ON v.option_group_id = g.id AND p.phone_type_id = v.value
 195 WHERE      c.id IN ( $ids )
 196 AND        p.phone IS NOT NULL
 197 ";
 198
 199   $dao = &CRM_Core_DAO::executeQuery($sql);
 200   while ($dao->fetch()) {
 201     $phone = [
 202       'id' => $dao->id,
 203       'contact_id' => $dao->contact_id,
 204       'location_type' => empty($dao->location_type) ? NULL : $dao->location_type,
 205       'phone' => $dao->phone,
 206       'phone_type' => empty($dao->phone_type) ? NULL : $dao->phone_type,
 207     ];
 208
 209     appendValue($values, $dao->id, 'phone', $phone);
 210   }
 211 }
 212
 213 /**
 214  * @param $contactIDs
 215  * @param $values
 216  */
 217 function getEmailInfo(&$contactIDs, &$values) {
 218   $ids = implode(',', $contactIDs);
 219
 220   $sql = "
 221 SELECT
 222   e.id as id,
 223   c.id as contact_id,
 224   l.name as location_type,
 225   e.email as email
 226 FROM      civicrm_contact c
 227 INNER JOIN civicrm_email          e  ON e.contact_id        = c.id
 228 LEFT  JOIN civicrm_location_type  l  ON e.location_type_id  = l.id
 229 WHERE      c.id IN ( $ids )
 230 AND        e.email IS NOT NULL
 231 ";
 232
 233   $dao = &CRM_Core_DAO::executeQuery($sql);
 234   while ($dao->fetch()) {
 235     $email = [
 236       'id' => $dao->id,
 237       'contact_id' => $dao->contact_id,
 238       'location_type' => empty($dao->location_type) ? NULL : $dao->location_type,
 239       'email' => $dao->email,
 240     ];
 241     appendValue($values, $dao->id, 'email', $email);
 242   }
 243 }
 244
 245 /**
 246  * @param $contactIDs
 247  * @param $values
 248  */
 249 function getAddressInfo(&$contactIDs, &$values) {
 250   $ids = implode(',', $contactIDs);
 251
 252   $sql = "
 253 SELECT     a.id as id,
 254            c.id as contact_id, l.name as location_type,
 255            a.street_address, a.supplemental_address_1, a.supplemental_address_2,
 256            a.supplemental_address_3,
 257            a.city, a.postal_code,
 258            s.name as state, co.name as country
 259 FROM       civicrm_contact c
 260 INNER JOIN civicrm_address        a  ON a.contact_id        = c.id
 261 LEFT  JOIN civicrm_location_type  l  ON a.location_type_id  = l.id
 262 LEFT  JOIN civicrm_state_province s  ON a.state_province_id = s.id
 263 LEFT  JOIN civicrm_country        co ON a.country_id        = co.id
 264 WHERE c.id IN ( $ids )
 265 ";
 266
 267   $fields = [
 268     'id',
 269     'contact_id',
 270     'location_type',
 271     'street_address',
 272     'supplemental_address_1',
 273     'supplemental_address_2',
 274     'supplemental_address_3',
 275     'city',
 276     'postal_code',
 277     'state',
 278     'country',
 279   ];
 280   $dao = &CRM_Core_DAO::executeQuery($sql);
 281   while ($dao->fetch()) {
 282     $address = [];
 283     foreach ($fields as $fld) {
 284       if (empty($dao->$fld)) {
 285         $address[$fld] = NULL;
 286       }
 287       else {
 288         $address[$fld] = $dao->$fld;
 289       }
 290     }
 291     appendValue($values, $dao->id, 'address', $address);
 292   }
 293 }
 294
 295 /**
 296  * @param $contactIDs
 297  * @param $values
 298  * @param $allContactIDs
 299  * @param $additionalContacts
 300  */
 301 function getRelationshipInfo(&$contactIDs, &$values, &$allContactIDs, &$additionalContacts) {
 302   // handle relationships only once
 303   static $_relationshipsHandled = [];
 304
 305   $ids = implode(',', $contactIDs);
 306
 307   $sql = "(
 308   SELECT     r.*
 309   FROM       civicrm_relationship r
 310   WHERE      r.contact_id_a IN ( $ids )
 311 ) UNION (
 312   SELECT     r.*
 313   FROM       civicrm_relationship r
 314   WHERE      r.contact_id_b IN ( $ids )
 315 )
 316 ";
 317
 318   $relationshipFields = getDBFields('CRM_Contact_DAO_Relationship');
 319   $fields = array_keys($relationshipFields);
 320   $dao = &CRM_Core_DAO::executeQuery($sql);
 321   while ($dao->fetch()) {
 322     if (isset($_relationshipsHandled[$dao->id])) {
 323       continue;
 324     }
 325     $_relationshipsHandled[$dao->id] = $dao->id;
 326
 327     $relationship = [];
 328     foreach ($fields as $fld) {
 329       if (empty($dao->$fld)) {
 330         $relationship[$fld] = NULL;
 331       }
 332       else {
 333         $relationship[$fld] = $dao->$fld;
 334       }
 335     }
 336     appendValue($values, $dao->id, 'relationship', $relationship);
 337
 338     addAdditionalContacts([
 339       $dao->contact_id_a,
 340       $dao->contact_id_b,
 341     ],
 342       $allContactIDs, $additionalContacts
 343     );
 344   }
 345 }
 346
 347 /**
 348  * @param $contactIDs
 349  * @param $values
 350  * @param $allContactIDs
 351  * @param $additionalContacts
 352  */
 353 function getActivityInfo(&$contactIDs, &$values, &$allContactIDs, &$additionalContacts) {
 354   static $_activitiesHandled = [];
 355
 356   $ids = implode(',', $contactIDs);
 357
 358   $sql = "(
 359   SELECT     a.*
 360   FROM       civicrm_activity a
 361   INNER JOIN civicrm_activity_assignment aa ON aa.activity_id = a.id
 362   WHERE      aa.assignee_contact_id IN ( $ids )
 363     AND      ( a.activity_type_id != 3 AND a.activity_type_id != 20 )
 364 ) UNION (
 365   SELECT     a.*
 366   FROM       civicrm_activity a
 367   INNER JOIN civicrm_activity_target at ON at.activity_id = a.id
 368   WHERE      at.target_contact_id IN ( $ids )
 369     AND      ( a.activity_type_id != 3 AND a.activity_type_id != 20 )
 370 )
 371 ";
 372
 373   $activityFields = &getDBFields('CRM_Activity_DAO_Activity');
 374   $fields = array_keys($activityFields);
 375
 376   $activityIDs = [];
 377   $dao = &CRM_Core_DAO::executeQuery($sql);
 378   while ($dao->fetch()) {
 379     if (isset($_activitiesHandled[$dao->id])) {
 380       continue;
 381     }
 382     $_activitiesHandled[$dao->id] = $dao->id;
 383     $activityIDs[] = $dao->id;
 384
 385     $activity = [];
 386     foreach ($fields as $fld) {
 387       if (empty($dao->$fld)) {
 388         $activity[$fld] = NULL;
 389       }
 390       else {
 391         $activity[$fld] = $dao->$fld;
 392       }
 393     }
 394
 395     appendValue($values, $dao->id, 'activity', $activity);
 396     addAdditionalContacts([$dao->source_contact_id],
 397       $allContactIDs, $additionalContacts
 398     );
 399   }
 400
 401   if (empty($activityIDs)) {
 402     return;
 403   }
 404
 405   $activityIDString = implode(",", $activityIDs);
 406
 407   // now get all assignee contact ids and target contact ids for this activity
 408   $sql = "SELECT * FROM civicrm_activity_assignment WHERE activity_id IN ($activityIDString)";
 409   $aaDAO = &CRM_Core_DAO::executeQuery($sql);
 410   $activityContacts = [];
 411   while ($aaDAO->fetch()) {
 412     $activityAssignee = [
 413       'id' => $aaDAO->id,
 414       'assignee_contact_id' => $aaDAO->assignee_contact_id,
 415       'activity_id' => $aaDAO->activity_id,
 416     ];
 417     appendValue($values, $aaDAO->id, 'activity_assignment', $activityAssignee);
 418     $activityContacts[] = $aaDAO->assignee_contact_id;
 419   }
 420
 421   $sql = "SELECT * FROM civicrm_activity_target WHERE activity_id IN ($activityIDString)";
 422   $atDAO = &CRM_Core_DAO::executeQuery($sql);
 423   while ($atDAO->fetch()) {
 424     $activityTarget = [
 425       'id' => $atDAO->id,
 426       'target_contact_id' => $atDAO->target_contact_id,
 427       'activity_id' => $atDAO->activity_id,
 428     ];
 429     appendValue($values, $atDAO->id, 'activity_target', $activityTarget);
 430     $activityContacts[] = $atDAO->target_contact_id;
 431   }
 432
 433   addAdditionalContacts($activityContacts, $allContactIDs, $additionalContacts);
 434 }
 435
 436 /**
 437  * @param $values
 438  * @param $id
 439  * @param $name
 440  * @param $value
 441  * @param bool $ignored
 442  */
 443 function appendValue(&$values, $id, $name, $value, $ignored = FALSE) {
 444   if (empty($value)) {
 445     return;
 446   }
 447
 448   if (!isset($values[$name])) {
 449     $values[$name] = [];
 450     $values[$name][] = array_keys($value);
 451   }
 452   $values[$name][] = array_values($value);
 453 }
 454
 455 /**
 456  * @param string $daoName
 457  *
 458  * @return mixed
 459  */
 460 function getDBFields($daoName) {
 461   static $_fieldsRetrieved = [];
 462
 463   if (!isset($_fieldsRetrieved[$daoName])) {
 464     $_fieldsRetrieved[$daoName] = [];
 465     $daoFile = str_replace('_',
 466         DIRECTORY_SEPARATOR,
 467         $daoName
 468       ) . '.php';
 469     include_once($daoFile);
 470
 471     $daoFields = &$daoName::fields();
 472     require_once 'CRM/Utils/Array.php';
 473
 474     foreach ($daoFields as $key => & $value) {
 475       $_fieldsRetrieved[$daoName][$value['name']] = [
 476         'uniqueName' => $key,
 477         'type' => $value['type'],
 478         'title' => CRM_Utils_Array::value('title', $value, NULL),
 479       ];
 480     }
 481   }
 482   return $_fieldsRetrieved[$daoName];
 483 }
 484
 485 /**
 486  * @param $contactIDs
 487  * @param $allContactIDs
 488  * @param $additionalContacts
 489  */
 490 function addAdditionalContacts($contactIDs, &$allContactIDs, &$additionalContacts) {
 491   foreach ($contactIDs as $cid) {
 492     if ($cid &&
 493       !isset($allContactIDs[$cid]) &&
 494       !isset($additionalContacts[$cid])
 495     ) {
 496       $additionalContacts[$cid] = $cid;
 497     }
 498   }
 499 }
 500
 501 /**
 502  * @param $values
 503  * @param $contactIDs
 504  * @param $allContactIDs
 505  */
 506 function run(&$values, &$contactIDs, &$allContactIDs) {
 507   $chunks = &splitContactIDs($contactIDs);
 508
 509   $additionalContactIDs = [];
 510
 511   foreach ($chunks as $chunk) {
 512     getValues($chunk, $values, $allContactIDs, $additionalContactIDs);
 513   }
 514
 515   if (!empty($additionalContactIDs)) {
 516     $allContactIDs = $allContactIDs + $additionalContactIDs;
 517     run($values, $additionalContactIDs, $allContactIDs);
 518   }
 519 }
 520
 521 $config = CRM_Core_Config::singleton();
 522 $config->userFramework = 'Soap';
 523 $config->userFrameworkClass = 'CRM_Utils_System_Soap';
 524 $config->userHookClass = 'CRM_Utils_Hook_Soap';
 525
 526 $sql = "
 527 SELECT id
 528 FROM civicrm_contact
 529 LIMIT 10
 530 ";
 531 $dao = &CRM_Core_DAO::executeQuery($sql);
 532
 533
 534 $contactIDs = [];
 535 while ($dao->fetch()) {
 536   $contactIDs[$dao->id] = $dao->id;
 537 }
 538
 539 $values = [];
 540 run($values, $contactIDs, $contactIDs);
 541
 542 $json = json_encode($values);
 543 echo $json;
 544 // print_r( json_decode( $json ) );
 545