CRM/Contact/BAO/Relationship.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  * Class CRM_Contact_BAO_Relationship.
  14  */
  15 class CRM_Contact_BAO_Relationship extends CRM_Contact_DAO_Relationship {
  16
  17   /**
  18    * Various constants to indicate different type of relationships.
  19    *
  20    * @var int
  21    */
  22   const ALL = 0, PAST = 1, DISABLED = 2, CURRENT = 4, INACTIVE = 8;
  23
  24   /**
  25    * Constants for is_permission fields.
  26    * Note: the slightly non-obvious ordering is due to history...
  27    */
  28   const NONE = 0, EDIT = 1, VIEW = 2;
  29
  30   /**
  31    * The list of column headers
  32    * @var array
  33    */
  34   private static $columnHeaders;
  35
  36   /**
  37    * Create function - use the API instead.
  38    *
  39    * Note that the previous create function has been renamed 'legacyCreateMultiple'
  40    * and this is new in 4.6
  41    * All existing calls have been changed to legacyCreateMultiple except the api call - however, it is recommended
  42    * that you call that as the end to end testing here is based on the api & refactoring may still be done.
  43    *
  44    * @param array $params
  45    *
  46    * @return \CRM_Contact_BAO_Relationship
  47    * @throws \CRM_Core_Exception
  48    */
  49   public static function create(&$params) {
  50
  51     $extendedParams = self::loadExistingRelationshipDetails($params);
  52     // When id is specified we always wan't to update, so we don't need to
  53     // check for duplicate relations.
  54     if (!isset($params['id']) && self::checkDuplicateRelationship($extendedParams, $extendedParams['contact_id_a'], $extendedParams['contact_id_b'], CRM_Utils_Array::value('id', $extendedParams, 0))) {
  55       throw new CRM_Core_Exception('Duplicate Relationship');
  56     }
  57     $params = $extendedParams;
  58     if (self::checkValidRelationship($params, $params, 0)) {
  59       throw new CRM_Core_Exception('Invalid Relationship');
  60     }
  61     $relationship = self::add($params);
  62     if (!empty($params['contact_id_a'])) {
  63       $ids = [
  64         'contactTarget' => $relationship->contact_id_b,
  65         'contact' => $params['contact_id_a'],
  66       ];
  67
  68       //CRM-16087 removed additional call to function relatedMemberships which is already called by disableEnableRelationship
  69       //resulting in membership being created twice
  70       if (array_key_exists('is_active', $params) && empty($params['is_active'])) {
  71         $action = CRM_Core_Action::DISABLE;
  72         $active = FALSE;
  73       }
  74       else {
  75         $action = CRM_Core_Action::ENABLE;
  76         $active = TRUE;
  77       }
  78       $id = empty($params['id']) ? $relationship->id : $params['id'];
  79       self::disableEnableRelationship($id, $action, $params, $ids, $active);
  80     }
  81
  82     if (empty($params['skipRecentView'])) {
  83       self::addRecent($params, $relationship);
  84     }
  85
  86     return $relationship;
  87   }
  88
  89   /**
  90    * Create multiple relationships for one contact.
  91    *
  92    * The relationship details are the same for each relationship except the secondary contact
  93    * id can be an array.
  94    *
  95    * @param array $params
  96    *   Parameters for creating multiple relationships.
  97    *   The parameters are the same as for relationship create function except that the non-primary
  98    *   end of the relationship should be an array of one or more contact IDs.
  99    * @param string $primaryContactLetter
 100    *   a or b to denote the primary contact for this action. The secondary may be multiple contacts
 101    *   and should be an array.
 102    *
 103    * @return array
 104    * @throws \CRM_Core_Exception
 105    */
 106   public static function createMultiple($params, $primaryContactLetter) {
 107     $secondaryContactLetter = ($primaryContactLetter == 'a') ? 'b' : 'a';
 108     $secondaryContactIDs = $params['contact_id_' . $secondaryContactLetter];
 109     $valid = $invalid = $duplicate = $saved = 0;
 110     $relationshipIDs = [];
 111     foreach ($secondaryContactIDs as $secondaryContactID) {
 112       try {
 113         $params['contact_id_' . $secondaryContactLetter] = $secondaryContactID;
 114         $relationship = civicrm_api3('relationship', 'create', $params);
 115         $relationshipIDs[] = $relationship['id'];
 116         $valid++;
 117       }
 118       catch (CiviCRM_API3_Exception $e) {
 119         switch ($e->getMessage()) {
 120           case 'Duplicate Relationship':
 121             $duplicate++;
 122             break;
 123
 124           case 'Invalid Relationship':
 125             $invalid++;
 126             break;
 127
 128           default:
 129             throw new CRM_Core_Exception('unknown relationship create error ' . $e->getMessage());
 130         }
 131       }
 132     }
 133
 134     return [
 135       'valid' => $valid,
 136       'invalid' => $invalid,
 137       'duplicate' => $duplicate,
 138       'saved' => $saved,
 139       'relationship_ids' => $relationshipIDs,
 140     ];
 141   }
 142
 143   /**
 144    * Only called from import now... plus one place outside of core & tests.
 145    *
 146    * @todo - deprecate more aggressively - will involve copying to the import
 147    * class, adding a deprecation notice here & removing from tests.
 148    *
 149    * Takes an associative array and creates a relationship object.
 150    *
 151    * @deprecated For single creates use the api instead (it's tested).
 152    * For multiple a new variant of this function needs to be written and migrated to as this is a bit
 153    * nasty
 154    *
 155    * @param array $params
 156    *   (reference ) an assoc array of name/value pairs.
 157    * @param array $ids
 158    *   The array that holds all the db ids.
 159    *   per http://wiki.civicrm.org/confluence/display/CRM/Database+layer
 160    *  "we are moving away from the $ids param "
 161    *
 162    * @return array
 163    * @throws \CRM_Core_Exception
 164    */
 165   public static function legacyCreateMultiple($params, $ids = []) {
 166     // clarify that the only key ever pass in the ids array is 'contact'
 167     // There is legacy handling for other keys but a universe search on
 168     // calls to this function (not supported to be called from outside core)
 169     // only returns 2 calls - one in CRM_Contact_Import_Parser_Contact
 170     // and the other in jma grant applications (CRM_Grant_Form_Grant_Confirm)
 171     // both only pass in contact as a key here.
 172     $ids = ['contact' => $ids['contact']];
 173     // Likewise neither place ever passed in relationshipID
 174     $relationshipId = NULL;
 175     // There is only ever one value passed in from the 2 places above that call
 176     // this - by clarifying here like this we can cleanup within this
 177     // function without having to do more universe searches.
 178     $relatedContactIDs = [key($params['contact_check'])];
 179
 180     if (!$relationshipId) {
 181       // creating a new relationship
 182       foreach ($relatedContactIDs as $relatedContactID) {
 183         // check if the relationship is valid between contacts.
 184         // step 1: check if the relationship is valid if not valid skip and keep the count
 185         // step 2: check the if two contacts already have a relationship if yes skip and keep the count
 186         // step 3: if valid relationship then add the relation and keep the count
 187
 188         // step 1
 189         $contactFields = self::setContactABFromIDs($params, $ids, $relatedContactID);
 190         $errors = self::checkValidRelationship($contactFields, $ids, $relatedContactID);
 191         if ($errors) {
 192           return [0, 0];
 193         }
 194
 195         //CRM-16978:check duplicate relationship as per case id.
 196         // https://issues.civicrm.org/jira/browse/CRM-16978
 197         if ($caseId = CRM_Utils_Array::value('case_id', $params)) {
 198           CRM_Core_Error::deprecatedWarning('this code is believed to be unreachable');
 199           $contactFields['case_id'] = $caseId;
 200         }
 201         if (
 202         self::checkDuplicateRelationship(
 203           $contactFields,
 204           CRM_Utils_Array::value('contact', $ids),
 205           // step 2
 206           $relatedContactID
 207         )
 208         ) {
 209           return [0, 1];
 210         }
 211
 212         $singleInstanceParams = array_merge($params, $contactFields);
 213         $relationship = self::add($singleInstanceParams);
 214       }
 215       // editing the relationship
 216     }
 217
 218     // do not add to recent items for import, CRM-4399
 219     if (!(!empty($params['skipRecentView']))) {
 220       self::addRecent($params, $relationship);
 221     }
 222
 223     return [1, 0];
 224   }
 225
 226   /**
 227    * This is the function that check/add if the relationship created is valid.
 228    *
 229    * @param array $params
 230    *   Array of name/value pairs.
 231    * @param array $ids
 232    *   The array that holds all the db ids.
 233    *
 234    * @return CRM_Contact_BAO_Relationship
 235    *
 236    * @throws \CiviCRM_API3_Exception
 237    */
 238   public static function add($params, $ids = []) {
 239     $params['id'] = CRM_Utils_Array::value('relationship', $ids, CRM_Utils_Array::value('id', $params));
 240
 241     $hook = 'create';
 242     if ($params['id']) {
 243       $hook = 'edit';
 244     }
 245     CRM_Utils_Hook::pre($hook, 'Relationship', $params['id'], $params);
 246
 247     $relationshipTypes = $params['relationship_type_id'] ?? NULL;
 248     // explode the string with _ to get the relationship type id
 249     // and to know which contact has to be inserted in
 250     // contact_id_a and which one in contact_id_b
 251     list($relationshipTypeID) = explode('_', $relationshipTypes);
 252
 253     $relationship = new CRM_Contact_BAO_Relationship();
 254     if (!empty($params['id'])) {
 255       $relationship->id = $params['id'];
 256       // Only load the relationship if we're missing required params
 257       $requiredParams = ['contact_id_a', 'contact_id_b', 'relationship_type_id'];
 258       foreach ($requiredParams as $requiredKey) {
 259         if (!isset($params[$requiredKey])) {
 260           $relationship->find(TRUE);
 261           break;
 262         }
 263       }
 264
 265     }
 266     $relationship->copyValues($params);
 267     // @todo we could probably set $params['relationship_type_id'] above but it's unclear
 268     //   what that would do with the code below this. So for now be conservative and set it manually.
 269     if (!empty($relationshipTypeID)) {
 270       $relationship->relationship_type_id = $relationshipTypeID;
 271     }
 272
 273     $params['contact_id_a'] = $relationship->contact_id_a;
 274     $params['contact_id_b'] = $relationship->contact_id_b;
 275
 276     // check if the relationship type is Head of Household then update the
 277     // household's primary contact with this contact.
 278     try {
 279       $headOfHouseHoldID = civicrm_api3('RelationshipType', 'getvalue', [
 280         'return' => "id",
 281         'name_a_b' => "Head of Household for",
 282       ]);
 283       if ($relationshipTypeID == $headOfHouseHoldID) {
 284         CRM_Contact_BAO_Household::updatePrimaryContact($relationship->contact_id_b, $relationship->contact_id_a);
 285       }
 286     }
 287     catch (Exception $e) {
 288       // No "Head of Household" relationship found so we skip specific processing
 289     }
 290
 291     if (!empty($params['id']) && self::isCurrentEmployerNeedingToBeCleared($relationship->toArray(), $params['id'], $relationshipTypeID)) {
 292       CRM_Contact_BAO_Contact_Utils::clearCurrentEmployer($relationship->contact_id_a);
 293     }
 294
 295     $dateFields = ['end_date', 'start_date'];
 296
 297     foreach (self::getdefaults() as $defaultField => $defaultValue) {
 298       if (isset($params[$defaultField])) {
 299         if (in_array($defaultField, $dateFields)) {
 300           $relationship->$defaultField = CRM_Utils_Date::format(CRM_Utils_Array::value($defaultField, $params));
 301           if (!$relationship->$defaultField) {
 302             $relationship->$defaultField = 'NULL';
 303           }
 304         }
 305         else {
 306           $relationship->$defaultField = $params[$defaultField];
 307         }
 308       }
 309       elseif (empty($params['id'])) {
 310         $relationship->$defaultField = $defaultValue;
 311       }
 312     }
 313
 314     $relationship->save();
 315     // is_current_employer is an optional parameter that triggers updating the employer_id field to reflect
 316     // the relationship being updated. As of writing only truthy versions of the parameter are respected.
 317     // https://github.com/civicrm/civicrm-core/pull/13331 attempted to cover both but stalled in QA
 318     // so currently we have a cut down version.
 319     if (!empty($params['is_current_employer'])) {
 320       if (!$relationship->relationship_type_id || !$relationship->contact_id_a || !$relationship->contact_id_b) {
 321         $relationship->fetch();
 322       }
 323       if (self::isRelationshipTypeCurrentEmployer($relationship->relationship_type_id)) {
 324         CRM_Contact_BAO_Contact_Utils::setCurrentEmployer([$relationship->contact_id_a => $relationship->contact_id_b]);
 325       }
 326     }
 327     // add custom field values
 328     if (!empty($params['custom'])) {
 329       CRM_Core_BAO_CustomValueTable::store($params['custom'], 'civicrm_relationship', $relationship->id);
 330     }
 331
 332     CRM_Utils_Hook::post($hook, 'Relationship', $relationship->id, $relationship);
 333
 334     return $relationship;
 335   }
 336
 337   /**
 338    * Add relationship to recent links.
 339    *
 340    * @param array $params
 341    * @param CRM_Contact_DAO_Relationship $relationship
 342    */
 343   public static function addRecent($params, $relationship) {
 344     $url = CRM_Utils_System::url('civicrm/contact/view/rel',
 345       "action=view&reset=1&id={$relationship->id}&cid={$relationship->contact_id_a}&context=home"
 346     );
 347     $session = CRM_Core_Session::singleton();
 348     $recentOther = [];
 349     if (($session->get('userID') == $relationship->contact_id_a) ||
 350       CRM_Contact_BAO_Contact_Permission::allow($relationship->contact_id_a, CRM_Core_Permission::EDIT)
 351     ) {
 352       $rType = substr(CRM_Utils_Array::value('relationship_type_id', $params), -3);
 353       $recentOther = [
 354         'editUrl' => CRM_Utils_System::url('civicrm/contact/view/rel',
 355           "action=update&reset=1&id={$relationship->id}&cid={$relationship->contact_id_a}&rtype={$rType}&context=home"
 356         ),
 357         'deleteUrl' => CRM_Utils_System::url('civicrm/contact/view/rel',
 358           "action=delete&reset=1&id={$relationship->id}&cid={$relationship->contact_id_a}&rtype={$rType}&context=home"
 359         ),
 360       ];
 361     }
 362     $title = CRM_Contact_BAO_Contact::displayName($relationship->contact_id_a) . ' (' . CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_RelationshipType',
 363         $relationship->relationship_type_id, 'label_a_b'
 364       ) . ' ' . CRM_Contact_BAO_Contact::displayName($relationship->contact_id_b) . ')';
 365
 366     CRM_Utils_Recent::add($title,
 367       $url,
 368       $relationship->id,
 369       'Relationship',
 370       $relationship->contact_id_a,
 371       NULL,
 372       $recentOther
 373     );
 374   }
 375
 376   /**
 377    * Load contact ids and relationship type id when doing a create call if not provided.
 378    *
 379    * There are are various checks done in create which require this information which is optional
 380    * when using id.
 381    *
 382    * @param array $params
 383    *   Parameters passed to create call.
 384    *
 385    * @return array
 386    *   Parameters with missing fields added if required.
 387    */
 388   public static function loadExistingRelationshipDetails($params) {
 389     if (!empty($params['contact_id_a'])
 390       && !empty($params['contact_id_b'])
 391       && is_numeric($params['relationship_type_id'])) {
 392       return $params;
 393     }
 394     if (empty($params['id'])) {
 395       return $params;
 396     }
 397
 398     $fieldsToFill = ['contact_id_a', 'contact_id_b', 'relationship_type_id'];
 399     $result = CRM_Core_DAO::executeQuery("SELECT " . implode(',', $fieldsToFill) . " FROM civicrm_relationship WHERE id = %1", [
 400       1 => [
 401         $params['id'],
 402         'Integer',
 403       ],
 404     ]);
 405     while ($result->fetch()) {
 406       foreach ($fieldsToFill as $field) {
 407         $params[$field] = !empty($params[$field]) ? $params[$field] : $result->$field;
 408       }
 409     }
 410     return $params;
 411   }
 412
 413   /**
 414    * Resolve passed in contact IDs to contact_id_a & contact_id_b.
 415    *
 416    * @param array $params
 417    * @param array $ids
 418    * @param null $contactID
 419    *
 420    * @return array
 421    * @throws \CRM_Core_Exception
 422    */
 423   public static function setContactABFromIDs($params, $ids = [], $contactID = NULL) {
 424     $returnFields = [];
 425
 426     // $ids['contact'] is deprecated but comes from legacyCreateMultiple function.
 427     if (empty($ids['contact'])) {
 428       if (!empty($params['id'])) {
 429         return self::loadExistingRelationshipDetails($params);
 430       }
 431       throw new CRM_Core_Exception('Cannot create relationship, insufficient contact IDs provided');
 432     }
 433     if (isset($params['relationship_type_id']) && !is_numeric($params['relationship_type_id'])) {
 434       $relationshipTypes = $params['relationship_type_id'] ?? NULL;
 435       list($relationshipTypeID, $first) = explode('_', $relationshipTypes);
 436       $returnFields['relationship_type_id'] = $relationshipTypeID;
 437
 438       foreach (['a', 'b'] as $contactLetter) {
 439         if (empty($params['contact_' . $contactLetter])) {
 440           if ($first == $contactLetter) {
 441             $returnFields['contact_id_' . $contactLetter] = $ids['contact'] ?? NULL;
 442           }
 443           else {
 444             $returnFields['contact_id_' . $contactLetter] = $contactID;
 445           }
 446         }
 447       }
 448     }
 449
 450     return $returnFields;
 451   }
 452
 453   /**
 454    * Specify defaults for creating a relationship.
 455    *
 456    * @return array
 457    *   array of defaults for creating relationship
 458    */
 459   public static function getdefaults() {
 460     return [
 461       'is_active' => 1,
 462       'is_permission_a_b' => self::NONE,
 463       'is_permission_b_a' => self::NONE,
 464       'description' => '',
 465       'start_date' => 'NULL',
 466       'case_id' => NULL,
 467       'end_date' => 'NULL',
 468     ];
 469   }
 470
 471   /**
 472    * Check if there is data to create the object.
 473    *
 474    * @param array $params
 475    *
 476    * @deprecated
 477    * @return bool
 478    */
 479   public static function dataExists($params) {
 480     CRM_Core_Error::deprecatedFunctionWarning('obsolete');
 481     return (isset($params['contact_check']) && is_array($params['contact_check']));
 482   }
 483
 484   /**
 485    * Get get list of relationship type based on the contact type.
 486    *
 487    * @param int $contactId
 488    *   This is the contact id of the current contact.
 489    * @param null $contactSuffix
 490    * @param string $relationshipId
 491    *   The id of the existing relationship if any.
 492    * @param string $contactType
 493    *   Contact type.
 494    * @param bool $all
 495    *   If true returns relationship types in both the direction.
 496    * @param string $column
 497    *   Name/label that going to retrieve from db.
 498    * @param bool $biDirectional
 499    * @param array $contactSubType
 500    *   Includes relationship types between this subtype.
 501    * @param bool $onlySubTypeRelationTypes
 502    *   If set only subtype which is passed by $contactSubType
 503    *   related relationship types get return
 504    *
 505    * @return array
 506    *   array reference of all relationship types with context to current contact.
 507    */
 508   public static function getContactRelationshipType(
 509     $contactId = NULL,
 510     $contactSuffix = NULL,
 511     $relationshipId = NULL,
 512     $contactType = NULL,
 513     $all = FALSE,
 514     $column = 'label',
 515     $biDirectional = TRUE,
 516     $contactSubType = NULL,
 517     $onlySubTypeRelationTypes = FALSE
 518   ) {
 519
 520     $relationshipType = [];
 521     $allRelationshipType = CRM_Core_PseudoConstant::relationshipType($column);
 522
 523     $otherContactType = NULL;
 524     if ($relationshipId) {
 525       $relationship = new CRM_Contact_DAO_Relationship();
 526       $relationship->id = $relationshipId;
 527       if ($relationship->find(TRUE)) {
 528         $contact = new CRM_Contact_DAO_Contact();
 529         $contact->id = ($relationship->contact_id_a == $contactId) ? $relationship->contact_id_b : $relationship->contact_id_a;
 530
 531         if ($contact->find(TRUE)) {
 532           $otherContactType = $contact->contact_type;
 533           //CRM-5125 for contact subtype specific relationshiptypes
 534           if ($contact->contact_sub_type) {
 535             $otherContactSubType = $contact->contact_sub_type;
 536           }
 537         }
 538       }
 539     }
 540
 541     $contactSubType = (array) $contactSubType;
 542     if ($contactId) {
 543       $contactType = CRM_Contact_BAO_Contact::getContactType($contactId);
 544       $contactSubType = CRM_Contact_BAO_Contact::getContactSubType($contactId);
 545     }
 546
 547     foreach ($allRelationshipType as $key => $value) {
 548       // the contact type is required or matches
 549       if (((!$value['contact_type_a']) ||
 550           $value['contact_type_a'] == $contactType
 551         ) &&
 552         // the other contact type is required or present or matches
 553         ((!$value['contact_type_b']) ||
 554           (!$otherContactType) ||
 555           $value['contact_type_b'] == $otherContactType
 556         ) &&
 557         (in_array($value['contact_sub_type_a'], $contactSubType) ||
 558           (!$value['contact_sub_type_a'] && !$onlySubTypeRelationTypes)
 559         )
 560       ) {
 561         $relationshipType[$key . '_a_b'] = $value["{$column}_a_b"];
 562       }
 563
 564       if (((!$value['contact_type_b']) ||
 565           $value['contact_type_b'] == $contactType
 566         ) &&
 567         ((!$value['contact_type_a']) ||
 568           (!$otherContactType) ||
 569           $value['contact_type_a'] == $otherContactType
 570         ) &&
 571         (in_array($value['contact_sub_type_b'], $contactSubType) ||
 572           (!$value['contact_sub_type_b'] && !$onlySubTypeRelationTypes)
 573         )
 574       ) {
 575         $relationshipType[$key . '_b_a'] = $value["{$column}_b_a"];
 576       }
 577
 578       if ($all) {
 579         $relationshipType[$key . '_a_b'] = $value["{$column}_a_b"];
 580         $relationshipType[$key . '_b_a'] = $value["{$column}_b_a"];
 581       }
 582     }
 583
 584     if ($biDirectional) {
 585       $relationshipType = self::removeRelationshipTypeDuplicates($relationshipType, $contactSuffix);
 586     }
 587
 588     // sort the relationshipType in ascending order CRM-7736
 589     asort($relationshipType);
 590     return $relationshipType;
 591   }
 592
 593   /**
 594    * Given a list of relationship types, return the list with duplicate types
 595    * removed, being careful to retain only the duplicate which matches the given
 596    * 'a_b' or 'b_a' suffix.
 597    *
 598    * @param array $relationshipTypeList A list of relationship types, in the format
 599    *   returned by self::getContactRelationshipType().
 600    * @param string $suffix Either 'a_b' or 'b_a'; defaults to 'a_b'
 601    *
 602    * @return array The modified value of $relationshipType
 603    */
 604   public static function removeRelationshipTypeDuplicates($relationshipTypeList, $suffix = NULL) {
 605     if (empty($suffix)) {
 606       $suffix = 'a_b';
 607     }
 608
 609     // Find those labels which are listed more than once.
 610     $duplicateValues = array_diff_assoc($relationshipTypeList, array_unique($relationshipTypeList));
 611
 612     // For each duplicate label, find its keys, and remove from $relationshipType
 613     // the key which does not match $suffix.
 614     foreach ($duplicateValues as $value) {
 615       $keys = array_keys($relationshipTypeList, $value);
 616       foreach ($keys as $key) {
 617         if (substr($key, -3) != $suffix) {
 618           unset($relationshipTypeList[$key]);
 619         }
 620       }
 621     }
 622     return $relationshipTypeList;
 623   }
 624
 625   /**
 626    * Delete current employer relationship.
 627    *
 628    * @param int $id
 629    * @param int $action
 630    *
 631    * @return CRM_Contact_DAO_Relationship
 632    */
 633   public static function clearCurrentEmployer($id, $action) {
 634     $relationship = new CRM_Contact_DAO_Relationship();
 635     $relationship->id = $id;
 636     $relationship->find(TRUE);
 637
 638     //to delete relationship between household and individual                                                                                          \
 639     //or between individual and organization
 640     if (($action & CRM_Core_Action::DISABLE) || ($action & CRM_Core_Action::DELETE)) {
 641       $relTypes = CRM_Utils_Array::index(['name_a_b'], CRM_Core_PseudoConstant::relationshipType('name'));
 642       if (
 643         (isset($relTypes['Employee of']) && $relationship->relationship_type_id == $relTypes['Employee of']['id']) ||
 644         (isset($relTypes['Household Member of']) && $relationship->relationship_type_id == $relTypes['Household Member of']['id'])
 645       ) {
 646         $sharedContact = new CRM_Contact_DAO_Contact();
 647         $sharedContact->id = $relationship->contact_id_a;
 648         $sharedContact->find(TRUE);
 649
 650         // CRM-15881 UPDATES
 651         // changed FROM "...relationship->relationship_type_id == 4..." TO "...relationship->relationship_type_id == 5..."
 652         // As the system should be looking for type "employer of" (id 5) and not "sibling of" (id 4)
 653         // As suggested by @davecivicrm, the employee relationship type id is fetched using the CRM_Core_DAO::getFieldValue() class and method, since these ids differ from system to system.
 654         $employerRelTypeId = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_RelationshipType', 'Employee of', 'id', 'name_a_b');
 655
 656         if ($relationship->relationship_type_id == $employerRelTypeId && $relationship->contact_id_b == $sharedContact->employer_id) {
 657           CRM_Contact_BAO_Contact_Utils::clearCurrentEmployer($relationship->contact_id_a);
 658         }
 659
 660       }
 661     }
 662     return $relationship;
 663   }
 664
 665   /**
 666    * Delete the relationship.
 667    *
 668    * @param int $id
 669    *   Relationship id.
 670    *
 671    * @return CRM_Contact_DAO_Relationship
 672    *
 673    * @throws \CRM_Core_Exception
 674    * @throws \CiviCRM_API3_Exception
 675    */
 676   public static function del($id) {
 677     // delete from relationship table
 678     CRM_Utils_Hook::pre('delete', 'Relationship', $id);
 679
 680     $relationship = self::clearCurrentEmployer($id, CRM_Core_Action::DELETE);
 681     $relationship->delete();
 682     if (CRM_Core_Permission::access('CiviMember')) {
 683       // create $params array which isrequired to delete memberships
 684       // of the related contacts.
 685       $params = [
 686         'relationship_type_id' => "{$relationship->relationship_type_id}_a_b",
 687         'contact_check' => [$relationship->contact_id_b => 1],
 688       ];
 689
 690       $ids = [];
 691       // calling relatedMemberships to delete the memberships of
 692       // related contacts.
 693       self::relatedMemberships($relationship->contact_id_a,
 694         $params,
 695         $ids,
 696         CRM_Core_Action::DELETE,
 697         FALSE
 698       );
 699     }
 700
 701     CRM_Core_Session::setStatus(ts('Selected relationship has been deleted successfully.'), ts('Record Deleted'), 'success');
 702
 703     CRM_Utils_Hook::post('delete', 'Relationship', $id, $relationship);
 704
 705     return $relationship;
 706   }
 707
 708   /**
 709    * Disable/enable the relationship.
 710    *
 711    * @param int $id
 712    *   Relationship id.
 713    *
 714    * @param int $action
 715    * @param array $params
 716    * @param array $ids
 717    * @param bool $active
 718    *
 719    * @throws \CRM_Core_Exception
 720    * @throws \CiviCRM_API3_Exception
 721    */
 722   public static function disableEnableRelationship($id, $action, $params = [], $ids = [], $active = FALSE) {
 723     $relationship = self::clearCurrentEmployer($id, $action);
 724
 725     if ($id) {
 726       // create $params array which is required to delete memberships
 727       // of the related contacts.
 728       if (empty($params)) {
 729         $params = [
 730           'relationship_type_id' => "{$relationship->relationship_type_id}_a_b",
 731           'contact_check' => [$relationship->contact_id_b => 1],
 732         ];
 733       }
 734       $contact_id_a = empty($params['contact_id_a']) ? $relationship->contact_id_a : $params['contact_id_a'];
 735       // calling relatedMemberships to delete/add the memberships of
 736       // related contacts.
 737       if ($action & CRM_Core_Action::DISABLE) {
 738         // @todo this could call a subset of the function that just relates to
 739         // cleaning up no-longer-inherited relationships
 740         CRM_Contact_BAO_Relationship::relatedMemberships($contact_id_a,
 741           $params,
 742           $ids,
 743           CRM_Core_Action::DELETE,
 744           $active
 745         );
 746       }
 747       elseif ($action & CRM_Core_Action::ENABLE) {
 748         $ids['contact'] = empty($ids['contact']) ? $contact_id_a : $ids['contact'];
 749         CRM_Contact_BAO_Relationship::relatedMemberships($contact_id_a,
 750           $params,
 751           $ids,
 752           empty($params['id']) ? CRM_Core_Action::ADD : CRM_Core_Action::UPDATE,
 753           $active
 754         );
 755       }
 756     }
 757   }
 758
 759   /**
 760    * Delete the object records that are associated with this contact.
 761    *
 762    * @param int $contactId
 763    *   Id of the contact to delete.
 764    */
 765   public static function deleteContact($contactId) {
 766     $relationship = new CRM_Contact_DAO_Relationship();
 767     $relationship->contact_id_a = $contactId;
 768     $relationship->delete();
 769
 770     $relationship = new CRM_Contact_DAO_Relationship();
 771     $relationship->contact_id_b = $contactId;
 772     $relationship->delete();
 773
 774     CRM_Contact_BAO_Household::updatePrimaryContact(NULL, $contactId);
 775   }
 776
 777   /**
 778    * Get the other contact in a relationship.
 779    *
 780    * @param int $id
 781    *   Relationship id.
 782    *
 783    * $returns  returns the contact ids in the relationship
 784    *
 785    * @return \CRM_Contact_DAO_Relationship
 786    */
 787   public static function getRelationshipByID($id) {
 788     $relationship = new CRM_Contact_DAO_Relationship();
 789
 790     $relationship->id = $id;
 791     $relationship->selectAdd();
 792     $relationship->selectAdd('contact_id_a, contact_id_b');
 793     $relationship->find(TRUE);
 794
 795     return $relationship;
 796   }
 797
 798   /**
 799    * Check if the relationship type selected between two contacts is correct.
 800    *
 801    * @param int $contact_a
 802    *   1st contact id.
 803    * @param int $contact_b
 804    *   2nd contact id.
 805    * @param int $relationshipTypeId
 806    *   Relationship type id.
 807    *
 808    * @return bool
 809    *   true if it is valid relationship else false
 810    */
 811   public static function checkRelationshipType($contact_a, $contact_b, $relationshipTypeId) {
 812     $relationshipType = new CRM_Contact_DAO_RelationshipType();
 813     $relationshipType->id = $relationshipTypeId;
 814     $relationshipType->selectAdd();
 815     $relationshipType->selectAdd('contact_type_a, contact_type_b, contact_sub_type_a, contact_sub_type_b');
 816     if ($relationshipType->find(TRUE)) {
 817       $contact_type_a = CRM_Contact_BAO_Contact::getContactType($contact_a);
 818       $contact_type_b = CRM_Contact_BAO_Contact::getContactType($contact_b);
 819
 820       $contact_sub_type_a = CRM_Contact_BAO_Contact::getContactSubType($contact_a);
 821       $contact_sub_type_b = CRM_Contact_BAO_Contact::getContactSubType($contact_b);
 822
 823       if (((!$relationshipType->contact_type_a) || ($relationshipType->contact_type_a == $contact_type_a)) &&
 824         ((!$relationshipType->contact_type_b) || ($relationshipType->contact_type_b == $contact_type_b)) &&
 825         ((!$relationshipType->contact_sub_type_a) || (in_array($relationshipType->contact_sub_type_a,
 826             $contact_sub_type_a
 827           ))) &&
 828         ((!$relationshipType->contact_sub_type_b) || (in_array($relationshipType->contact_sub_type_b,
 829             $contact_sub_type_b
 830           )))
 831       ) {
 832         return TRUE;
 833       }
 834       else {
 835         return FALSE;
 836       }
 837     }
 838     return FALSE;
 839   }
 840
 841   /**
 842    * This function does the validtion for valid relationship.
 843    *
 844    * @param array $params
 845    *   This array contains the values there are subitted by the form.
 846    * @param array $ids
 847    *   The array that holds all the db ids.
 848    * @param int $contactId
 849    *   This is contact id for adding relationship.
 850    *
 851    * @return string
 852    */
 853   public static function checkValidRelationship($params, $ids, $contactId) {
 854     $errors = '';
 855     // function to check if the relationship selected is correct
 856     // i.e. employer relationship can exit between Individual and Organization (not between Individual and Individual)
 857     if (!CRM_Contact_BAO_Relationship::checkRelationshipType($params['contact_id_a'], $params['contact_id_b'],
 858       $params['relationship_type_id'])) {
 859       $errors = 'Please select valid relationship between these two contacts.';
 860     }
 861     return $errors;
 862   }
 863
 864   /**
 865    * This function checks for duplicate relationship.
 866    *
 867    * @param array $params
 868    *   An assoc array of name/value pairs.
 869    * @param int $id
 870    *   This the id of the contact whom we are adding relationship.
 871    * @param int $contactId
 872    *   This is contact id for adding relationship.
 873    * @param int $relationshipId
 874    *   This is relationship id for the contact.
 875    *
 876    * @return bool
 877    *   true if record exists else false
 878    */
 879   public static function checkDuplicateRelationship($params, $id, $contactId = 0, $relationshipId = 0) {
 880     $relationshipTypeId = $params['relationship_type_id'] ?? NULL;
 881     list($type) = explode('_', $relationshipTypeId);
 882
 883     $queryString = "
 884 SELECT id
 885 FROM   civicrm_relationship
 886 WHERE  relationship_type_id = " . CRM_Utils_Type::escape($type, 'Integer');
 887
 888     /*
 889      * CRM-11792 - date fields from API are in ISO format, but this function
 890      * supports date arrays BAO has increasingly standardised to ISO format
 891      * so I believe this function should support ISO rather than make API
 892      * format it - however, need to support array format for now to avoid breakage
 893      * @ time of writing this function is called from Relationship::legacyCreateMultiple (twice)
 894      * CRM_BAO_Contact_Utils::clearCurrentEmployer (seemingly without dates)
 895      * CRM_Contact_Form_Task_AddToOrganization::postProcess &
 896      * CRM_Contact_Form_Task_AddToHousehold::postProcess
 897      * (I don't think the last 2 support dates but not sure
 898      */
 899
 900     $dateFields = ['end_date', 'start_date'];
 901     foreach ($dateFields as $dateField) {
 902       if (array_key_exists($dateField, $params)) {
 903         if (empty($params[$dateField]) || $params[$dateField] == 'null') {
 904           //this is most likely coming from an api call & probably loaded
 905           // from the DB to deal with some of the
 906           // other myriad of excessive checks still in place both in
 907           // the api & the create functions
 908           $queryString .= " AND $dateField IS NULL";
 909           continue;
 910         }
 911         elseif (is_array($params[$dateField])) {
 912           $queryString .= " AND $dateField = " .
 913             CRM_Utils_Type::escape(CRM_Utils_Date::format($params[$dateField]), 'Date');
 914         }
 915         else {
 916           $queryString .= " AND $dateField = " .
 917             CRM_Utils_Type::escape($params[$dateField], 'Date');
 918         }
 919       }
 920     }
 921
 922     $queryString .=
 923       ' AND ( ( contact_id_a = ' . CRM_Utils_Type::escape($id, 'Integer') .
 924       ' AND contact_id_b = ' . CRM_Utils_Type::escape($contactId, 'Integer') .
 925       ' ) OR ( contact_id_a = ' . CRM_Utils_Type::escape($contactId, 'Integer') .
 926       ' AND contact_id_b = ' . CRM_Utils_Type::escape($id, 'Integer') . " ) ) ";
 927
 928     //if caseId is provided, include it duplicate checking.
 929     if ($caseId = CRM_Utils_Array::value('case_id', $params)) {
 930       $queryString .= ' AND case_id = ' . CRM_Utils_Type::escape($caseId, 'Integer');
 931     }
 932
 933     if ($relationshipId) {
 934       $queryString .= ' AND id !=' . CRM_Utils_Type::escape($relationshipId, 'Integer');
 935     }
 936
 937     $relationship = CRM_Core_DAO::executeQuery($queryString);
 938     while ($relationship->fetch()) {
 939       // Check whether the custom field values are identical.
 940       if (self::checkDuplicateCustomFields($params['custom'] ?? [], $relationship->id)) {
 941         return TRUE;
 942       }
 943     }
 944     return FALSE;
 945   }
 946
 947   /**
 948    * this function checks whether the values of the custom fields in $params are
 949    * the same as the values of the custom fields of the relation with given
 950    * $relationshipId.
 951    *
 952    * @param array $params an assoc array of name/value pairs
 953    * @param int $relationshipId ID of an existing duplicate relation
 954    *
 955    * @return boolean true if custom field values are identical
 956    * @access private
 957    * @static
 958    */
 959   private static function checkDuplicateCustomFields($params, $relationshipId) {
 960     // Get the custom values of the existing relationship.
 961     $existingValues = CRM_Core_BAO_CustomValueTable::getEntityValues($relationshipId, 'Relationship');
 962     // Create a similar array for the new relationship.
 963     $newValues = [];
 964     if (!is_array($params)) {
 965       // No idea when this would happen....
 966       CRM_Core_Error::deprecatedWarning('params should be an array');
 967     }
 968     else {
 969       // $params seems to be an array, as it should be. Each value is again an array.
 970       // This array contains one value (key -1), and this value seems to be
 971       // an array with the information about the custom value.
 972       foreach ($params as $value) {
 973         foreach ($value as $customValue) {
 974           $newValues[$customValue['custom_field_id']] = $customValue['value'];
 975         }
 976       }
 977     }
 978
 979     // Calculate difference between arrays. If the only key-value pairs
 980     // that are in one array but not in the other are empty, the
 981     // custom fields are considered to be equal.
 982     // See https://github.com/civicrm/civicrm-core/pull/6515#issuecomment-137985667
 983     $diff1 = array_diff_assoc($existingValues, $newValues);
 984     $diff2 = array_diff_assoc($newValues, $existingValues);
 985
 986     return !array_filter($diff1) && !array_filter($diff2);
 987   }
 988
 989   /**
 990    * Update the is_active flag in the db.
 991    *
 992    * @param int $id
 993    *   Id of the database record.
 994    * @param bool $is_active
 995    *   Value we want to set the is_active field.
 996    *
 997    * @return bool
 998    *
 999    * @throws CiviCRM_API3_Exception
1000    */
1001   public static function setIsActive($id, $is_active) {
1002     // as both the create & add functions have a bunch of logic in them that
1003     // doesn't seem to cope with a normal update we will call the api which
1004     // has tested handling for this
1005     // however, a longer term solution would be to simplify the add, create & api functions
1006     // to be more standard. It is debatable @ that point whether it's better to call the BAO
1007     // direct as the api is more tested.
1008     $result = civicrm_api('relationship', 'create', [
1009       'id' => $id,
1010       'is_active' => $is_active,
1011       'version' => 3,
1012     ]);
1013
1014     if (is_array($result) && !empty($result['is_error']) && $result['error_message'] != 'Duplicate Relationship') {
1015       throw new CiviCRM_API3_Exception($result['error_message'], CRM_Utils_Array::value('error_code', $result, 'undefined'), $result);
1016     }
1017
1018     return TRUE;
1019   }
1020
1021   /**
1022    * Fetch a relationship object and store the values in the values array.
1023    *
1024    * @param array $params
1025    *   Input parameters to find object.
1026    * @param array $values
1027    *   Output values of the object.
1028    *
1029    * @return array
1030    *   (reference)   the values that could be potentially assigned to smarty
1031    */
1032   public static function &getValues(&$params, &$values) {
1033     if (empty($params)) {
1034       return NULL;
1035     }
1036     $v = [];
1037
1038     // get the specific number of relationship or all relationships.
1039     if (!empty($params['numRelationship'])) {
1040       $v['data'] = &CRM_Contact_BAO_Relationship::getRelationship($params['contact_id'], NULL, $params['numRelationship']);
1041     }
1042     else {
1043       $v['data'] = CRM_Contact_BAO_Relationship::getRelationship($params['contact_id']);
1044     }
1045
1046     // get the total count of relationships
1047     $v['totalCount'] = count($v['data']);
1048
1049     $values['relationship']['data'] = &$v['data'];
1050     $values['relationship']['totalCount'] = &$v['totalCount'];
1051
1052     return $v;
1053   }
1054
1055   /**
1056    * Helper function to form the sql for relationship retrieval.
1057    *
1058    * @param int $contactId
1059    *   Contact id.
1060    * @param int $status
1061    *   (check const at top of file).
1062    * @param int $numRelationship
1063    *   No of relationships to display (limit).
1064    * @param int $count
1065    *   Get the no of relationships.
1066    * $param int $relationshipId relationship id
1067    * @param int $relationshipId
1068    * @param string $direction
1069    *   The direction we are interested in a_b or b_a.
1070    * @param array $params
1071    *   Array of extra values including relationship_type_id per api spec.
1072    *
1073    * @return array
1074    *   [select, from, where]
1075    *
1076    * @throws \CRM_Core_Exception
1077    * @throws \CiviCRM_API3_Exception
1078    */
1079   public static function makeURLClause($contactId, $status, $numRelationship, $count, $relationshipId, $direction, $params = []) {
1080     $select = $from = $where = '';
1081
1082     $select = '( ';
1083     if ($count) {
1084       if ($direction === 'a_b') {
1085         $select .= ' SELECT count(DISTINCT civicrm_relationship.id) as cnt1, 0 as cnt2 ';
1086       }
1087       else {
1088         $select .= ' SELECT 0 as cnt1, count(DISTINCT civicrm_relationship.id) as cnt2 ';
1089       }
1090     }
1091     else {
1092       $select .= ' SELECT civicrm_relationship.id as civicrm_relationship_id,
1093                               civicrm_contact.sort_name as sort_name,
1094                               civicrm_contact.display_name as display_name,
1095                               civicrm_contact.job_title as job_title,
1096                               civicrm_contact.employer_id as employer_id,
1097                               civicrm_contact.organization_name as organization_name,
1098                               civicrm_address.street_address as street_address,
1099                               civicrm_address.city as city,
1100                               civicrm_address.postal_code as postal_code,
1101                               civicrm_state_province.abbreviation as state,
1102                               civicrm_country.name as country,
1103                               civicrm_email.email as email,
1104                               civicrm_contact.contact_type as contact_type,
1105                               civicrm_contact.contact_sub_type as contact_sub_type,
1106                               civicrm_phone.phone as phone,
1107                               civicrm_contact.id as civicrm_contact_id,
1108                               civicrm_relationship.contact_id_b as contact_id_b,
1109                               civicrm_relationship.contact_id_a as contact_id_a,
1110                               civicrm_relationship_type.id as civicrm_relationship_type_id,
1111                               civicrm_relationship.start_date as start_date,
1112                               civicrm_relationship.end_date as end_date,
1113                               civicrm_relationship.description as description,
1114                               civicrm_relationship.is_active as is_active,
1115                               civicrm_relationship.is_permission_a_b as is_permission_a_b,
1116                               civicrm_relationship.is_permission_b_a as is_permission_b_a,
1117                               civicrm_relationship.case_id as case_id';
1118
1119       if ($direction === 'a_b') {
1120         $select .= ', civicrm_relationship_type.label_a_b as label_a_b,
1121                               civicrm_relationship_type.label_b_a as relation ';
1122       }
1123       else {
1124         $select .= ', civicrm_relationship_type.label_a_b as label_a_b,
1125                               civicrm_relationship_type.label_a_b as relation ';
1126       }
1127     }
1128
1129     $from = '
1130       FROM  civicrm_relationship
1131 INNER JOIN  civicrm_relationship_type ON ( civicrm_relationship.relationship_type_id = civicrm_relationship_type.id )
1132 INNER JOIN  civicrm_contact ';
1133     if ($direction === 'a_b') {
1134       $from .= 'ON ( civicrm_contact.id = civicrm_relationship.contact_id_a ) ';
1135     }
1136     else {
1137       $from .= 'ON ( civicrm_contact.id = civicrm_relationship.contact_id_b ) ';
1138     }
1139
1140     if (!$count) {
1141       $from .= '
1142 LEFT JOIN  civicrm_address ON (civicrm_address.contact_id = civicrm_contact.id AND civicrm_address.is_primary = 1)
1143 LEFT JOIN  civicrm_phone   ON (civicrm_phone.contact_id = civicrm_contact.id AND civicrm_phone.is_primary = 1)
1144 LEFT JOIN  civicrm_email   ON (civicrm_email.contact_id = civicrm_contact.id AND civicrm_email.is_primary = 1)
1145 LEFT JOIN  civicrm_state_province ON (civicrm_address.state_province_id = civicrm_state_province.id)
1146 LEFT JOIN  civicrm_country ON (civicrm_address.country_id = civicrm_country.id)
1147 ';
1148     }
1149
1150     $where = 'WHERE ( 1 )';
1151     if ($contactId) {
1152       if ($direction === 'a_b') {
1153         $where .= ' AND civicrm_relationship.contact_id_b = ' . CRM_Utils_Type::escape($contactId, 'Positive');
1154       }
1155       else {
1156         $where .= ' AND civicrm_relationship.contact_id_a = ' . CRM_Utils_Type::escape($contactId, 'Positive') . '
1157                     AND civicrm_relationship.contact_id_a != civicrm_relationship.contact_id_b ';
1158       }
1159     }
1160     if ($relationshipId) {
1161       $where .= ' AND civicrm_relationship.id = ' . CRM_Utils_Type::escape($relationshipId, 'Positive');
1162     }
1163
1164     $date = date('Y-m-d');
1165     if ($status == self::PAST) {
1166       //this case for showing past relationship
1167       $where .= ' AND civicrm_relationship.is_active = 1 ';
1168       $where .= " AND civicrm_relationship.end_date < '" . $date . "'";
1169     }
1170     elseif ($status == self::DISABLED) {
1171       // this case for showing disabled relationship
1172       $where .= ' AND civicrm_relationship.is_active = 0 ';
1173     }
1174     elseif ($status == self::CURRENT) {
1175       //this case for showing current relationship
1176       $where .= ' AND civicrm_relationship.is_active = 1 ';
1177       $where .= " AND (civicrm_relationship.end_date >= '" . $date . "' OR civicrm_relationship.end_date IS NULL) ";
1178     }
1179     elseif ($status == self::INACTIVE) {
1180       //this case for showing inactive relationships
1181       $where .= " AND (civicrm_relationship.end_date < '" . $date . "'";
1182       $where .= ' OR civicrm_relationship.is_active = 0 )';
1183     }
1184
1185     // CRM-6181
1186     $where .= ' AND civicrm_contact.is_deleted = 0';
1187     if (!empty($params['membership_type_id']) && empty($params['relationship_type_id'])) {
1188       $where .= self::membershipTypeToRelationshipTypes($params, $direction);
1189     }
1190     if (!empty($params['relationship_type_id'])) {
1191       if (is_array($params['relationship_type_id'])) {
1192         $where .= " AND " . CRM_Core_DAO::createSQLFilter('relationship_type_id', $params['relationship_type_id'], 'Integer');
1193       }
1194       else {
1195         $where .= ' AND relationship_type_id = ' . CRM_Utils_Type::escape($params['relationship_type_id'], 'Positive');
1196       }
1197     }
1198     if ($direction === 'a_b') {
1199       $where .= ' ) UNION ';
1200     }
1201     else {
1202       $where .= ' ) ';
1203     }
1204
1205     return [$select, $from, $where];
1206   }
1207
1208   /**
1209    * Get a list of relationships.
1210    *
1211    * @param int $contactId
1212    *   Contact id.
1213    * @param int $status
1214    *   1: Past 2: Disabled 3: Current.
1215    * @param int $numRelationship
1216    *   No of relationships to display (limit).
1217    * @param int $count
1218    *   Get the no of relationships.
1219    * @param int $relationshipId
1220    * @param array $links
1221    *   the list of links to display
1222    * @param int $permissionMask
1223    *   the permission mask to be applied for the actions
1224    * @param bool $permissionedContact
1225    *   to return only permissioned Contact
1226    * @param array $params
1227    * @param bool $includeTotalCount
1228    *   Should we return a count of total accessable relationships
1229    *
1230    * @return array|int
1231    *   relationship records
1232    *
1233    * @throws \CRM_Core_Exception
1234    * @throws \CiviCRM_API3_Exception
1235    */
1236   public static function getRelationship(
1237     $contactId = NULL,
1238     $status = 0, $numRelationship = 0,
1239     $count = 0, $relationshipId = 0,
1240     $links = NULL, $permissionMask = NULL,
1241     $permissionedContact = FALSE,
1242     $params = [], $includeTotalCount = FALSE
1243   ) {
1244     $values = [];
1245     if (!$contactId && !$relationshipId) {
1246       return $values;
1247     }
1248
1249     list($select1, $from1, $where1) = self::makeURLClause($contactId, $status, $numRelationship,
1250       $count, $relationshipId, 'a_b', $params
1251     );
1252     list($select2, $from2, $where2) = self::makeURLClause($contactId, $status, $numRelationship,
1253       $count, $relationshipId, 'b_a', $params
1254     );
1255
1256     $order = $limit = '';
1257     if (!$count) {
1258       if (empty($params['sort'])) {
1259         $order = ' ORDER BY civicrm_relationship_type_id, sort_name ';
1260       }
1261       else {
1262         $order = " ORDER BY {$params['sort']} ";
1263       }
1264
1265       $offset = 0;
1266       if (!empty($params['offset']) && $params['offset'] > 0) {
1267         $offset = $params['offset'];
1268       }
1269
1270       if ($numRelationship) {
1271         $limit = " LIMIT {$offset}, $numRelationship";
1272       }
1273     }
1274
1275     // building the query string
1276     $queryString = $select1 . $from1 . $where1 . $select2 . $from2 . $where2;
1277
1278     $relationship = new CRM_Contact_DAO_Relationship();
1279
1280     $relationship->query($queryString . $order . $limit);
1281     $row = [];
1282     if ($count) {
1283       $relationshipCount = 0;
1284       while ($relationship->fetch()) {
1285         $relationshipCount += $relationship->cnt1 + $relationship->cnt2;
1286       }
1287       return $relationshipCount;
1288     }
1289     else {
1290
1291       if ($includeTotalCount) {
1292         $values['total_relationships'] = CRM_Core_DAO::singleValueQuery("SELECT count(*) FROM ({$queryString}) AS r");
1293       }
1294
1295       $mask = NULL;
1296       if ($status != self::INACTIVE) {
1297         if ($links) {
1298           $mask = array_sum(array_keys($links));
1299           if ($mask & CRM_Core_Action::DISABLE) {
1300             $mask -= CRM_Core_Action::DISABLE;
1301           }
1302           if ($mask & CRM_Core_Action::ENABLE) {
1303             $mask -= CRM_Core_Action::ENABLE;
1304           }
1305
1306           if ($status == self::CURRENT) {
1307             $mask |= CRM_Core_Action::DISABLE;
1308           }
1309           elseif ($status == self::DISABLED) {
1310             $mask |= CRM_Core_Action::ENABLE;
1311           }
1312         }
1313         // temporary hold the value of $mask.
1314         $tempMask = $mask;
1315       }
1316
1317       while ($relationship->fetch()) {
1318         $rid = $relationship->civicrm_relationship_id;
1319         $cid = $relationship->civicrm_contact_id;
1320
1321         if ($permissionedContact &&
1322           (!CRM_Contact_BAO_Contact_Permission::allow($cid))
1323         ) {
1324           continue;
1325         }
1326         if ($status != self::INACTIVE && $links) {
1327           // assign the original value to $mask
1328           $mask = $tempMask;
1329           // display action links if $cid has edit permission for the relationship.
1330           if (!($permissionMask & CRM_Core_Permission::EDIT) && CRM_Contact_BAO_Contact_Permission::allow($cid, CRM_Core_Permission::EDIT)) {
1331             $permissions[] = CRM_Core_Permission::EDIT;
1332             $permissions[] = CRM_Core_Permission::DELETE;
1333             $permissionMask = CRM_Core_Action::mask($permissions);
1334           }
1335           $mask = $mask & $permissionMask;
1336         }
1337         $values[$rid]['id'] = $rid;
1338         $values[$rid]['cid'] = $cid;
1339         $values[$rid]['contact_id_a'] = $relationship->contact_id_a;
1340         $values[$rid]['contact_id_b'] = $relationship->contact_id_b;
1341         $values[$rid]['contact_type'] = $relationship->contact_type;
1342         $values[$rid]['contact_sub_type'] = $relationship->contact_sub_type;
1343         $values[$rid]['relationship_type_id'] = $relationship->civicrm_relationship_type_id;
1344         $values[$rid]['relation'] = $relationship->relation;
1345         $values[$rid]['name'] = $relationship->sort_name;
1346         $values[$rid]['display_name'] = $relationship->display_name;
1347         $values[$rid]['job_title'] = $relationship->job_title;
1348         $values[$rid]['email'] = $relationship->email;
1349         $values[$rid]['phone'] = $relationship->phone;
1350         $values[$rid]['employer_id'] = $relationship->employer_id;
1351         $values[$rid]['organization_name'] = $relationship->organization_name;
1352         $values[$rid]['country'] = $relationship->country;
1353         $values[$rid]['city'] = $relationship->city;
1354         $values[$rid]['state'] = $relationship->state;
1355         $values[$rid]['start_date'] = $relationship->start_date;
1356         $values[$rid]['end_date'] = $relationship->end_date;
1357         $values[$rid]['description'] = $relationship->description;
1358         $values[$rid]['is_active'] = $relationship->is_active;
1359         $values[$rid]['is_permission_a_b'] = $relationship->is_permission_a_b;
1360         $values[$rid]['is_permission_b_a'] = $relationship->is_permission_b_a;
1361         $values[$rid]['case_id'] = $relationship->case_id;
1362
1363         if ($status) {
1364           $values[$rid]['status'] = $status;
1365         }
1366
1367         $values[$rid]['civicrm_relationship_type_id'] = $relationship->civicrm_relationship_type_id;
1368
1369         if ($relationship->contact_id_a == $contactId) {
1370           $values[$rid]['rtype'] = 'a_b';
1371         }
1372         else {
1373           $values[$rid]['rtype'] = 'b_a';
1374         }
1375
1376         if ($links) {
1377           $replace = [
1378             'id' => $rid,
1379             'rtype' => $values[$rid]['rtype'],
1380             'cid' => $contactId,
1381             'cbid' => $values[$rid]['cid'],
1382             'caseid' => $values[$rid]['case_id'],
1383             'clientid' => $contactId,
1384           ];
1385
1386           if ($status == self::INACTIVE) {
1387             // setting links for inactive relationships
1388             $mask = array_sum(array_keys($links));
1389             if (!$values[$rid]['is_active']) {
1390               $mask -= CRM_Core_Action::DISABLE;
1391             }
1392             else {
1393               $mask -= CRM_Core_Action::ENABLE;
1394               $mask -= CRM_Core_Action::DISABLE;
1395             }
1396             $mask = $mask & $permissionMask;
1397           }
1398
1399           // Give access to manage case link by copying to MAX_ACTION index temporarily, depending on case permission of user.
1400           if ($values[$rid]['case_id']) {
1401             // Borrowed logic from CRM_Case_Page_Tab
1402             $hasCaseAccess = FALSE;
1403             if (CRM_Core_Permission::check('access all cases and activities')) {
1404               $hasCaseAccess = TRUE;
1405             }
1406             else {
1407               $userCases = CRM_Case_BAO_Case::getCases(FALSE);
1408               if (array_key_exists($values[$rid]['case_id'], $userCases)) {
1409                 $hasCaseAccess = TRUE;
1410               }
1411             }
1412
1413             if ($hasCaseAccess) {
1414               // give access by copying to MAX_ACTION temporarily, otherwise leave at NONE which won't display
1415               $links[CRM_Core_Action::MAX_ACTION] = $links[CRM_Core_Action::NONE];
1416               $links[CRM_Core_Action::MAX_ACTION]['name'] = ts('Manage Case #%1', [1 => $values[$rid]['case_id']]);
1417               $links[CRM_Core_Action::MAX_ACTION]['class'] = 'no-popup';
1418
1419               // Also make sure we have the right client cid since can get here from multiple relationship tabs.
1420               if ($values[$rid]['rtype'] == 'b_a') {
1421                 $replace['clientid'] = $values[$rid]['cid'];
1422               }
1423               $values[$rid]['case'] = '<a href="' . CRM_Utils_System::url('civicrm/case/ajax/details', sprintf('caseId=%d&cid=%d&snippet=4', $values[$rid]['case_id'], $values[$rid]['cid'])) . '" class="action-item crm-hover-button crm-summary-link"><i class="crm-i fa-folder-open-o" aria-hidden="true"></i></a>';
1424             }
1425           }
1426
1427           $values[$rid]['action'] = CRM_Core_Action::formLink(
1428             $links,
1429             $mask,
1430             $replace,
1431             ts('more'),
1432             FALSE,
1433             'relationship.selector.row',
1434             'Relationship',
1435             $rid);
1436           unset($links[CRM_Core_Action::MAX_ACTION]);
1437         }
1438       }
1439
1440       return $values;
1441     }
1442   }
1443
1444   /**
1445    * Get list of relationship type based on the target contact type.
1446    * Both directions of relationships are included if their labels are not the same.
1447    *
1448    * @param string $targetContactType
1449    *   A valid contact type (may be Individual, Organization, Household).
1450    *
1451    * @return array
1452    *   array reference of all relationship types with context to current contact type.
1453    */
1454   public static function getRelationType($targetContactType) {
1455     $relationshipType = [];
1456     $allRelationshipType = CRM_Core_PseudoConstant::relationshipType();
1457
1458     foreach ($allRelationshipType as $key => $type) {
1459       if ($type['contact_type_b'] == $targetContactType || empty($type['contact_type_b'])) {
1460         $relationshipType[$key . '_a_b'] = $type['label_a_b'];
1461       }
1462       if (($type['contact_type_a'] == $targetContactType || empty($type['contact_type_a']))
1463         && $type['label_a_b'] != $type['label_b_a']
1464       ) {
1465         $relationshipType[$key . '_b_a'] = $type['label_b_a'];
1466       }
1467     }
1468
1469     return $relationshipType;
1470   }
1471
1472   /**
1473    * Create / update / delete membership for related contacts.
1474    *
1475    * This function will create/update/delete membership for related
1476    * contact based on 1) contact have active membership 2) that
1477    * membership is is extedned by the same relationship type to that
1478    * of the existing relationship.
1479    *
1480    * @param int $contactId
1481    *   contact id.
1482    * @param array $params
1483    *   array of values submitted by POST.
1484    * @param array $ids
1485    *   array of ids.
1486    * @param \const|int $action which action called this function
1487    *
1488    * @param bool $active
1489    *
1490    * @throws \CRM_Core_Exception
1491    * @throws \CiviCRM_API3_Exception
1492    */
1493   public static function relatedMemberships($contactId, $params, $ids, $action = CRM_Core_Action::ADD, $active = TRUE) {
1494     // Check the end date and set the status of the relationship
1495     // accordingly.
1496     $status = self::CURRENT;
1497     $targetContact = $targetContact = CRM_Utils_Array::value('contact_check', $params, []);
1498     $today = date('Ymd');
1499
1500     // If a relationship hasn't yet started, just return for now
1501     // TODO: handle edge-case of updating start_date of an existing relationship
1502     if (!empty($params['start_date'])) {
1503       $startDate = substr(CRM_Utils_Date::format($params['start_date']), 0, 8);
1504       if ($today < $startDate) {
1505         return;
1506       }
1507     }
1508
1509     if (!empty($params['end_date'])) {
1510       $endDate = substr(CRM_Utils_Date::format($params['end_date']), 0, 8);
1511       if ($today > $endDate) {
1512         $status = self::PAST;
1513       }
1514     }
1515
1516     if (($action & CRM_Core_Action::ADD) && ($status & self::PAST)) {
1517       // If relationship is PAST and action is ADD, do nothing.
1518       return;
1519     }
1520
1521     $rel = explode('_', $params['relationship_type_id']);
1522
1523     $relTypeId = $rel[0];
1524     if (!empty($rel[1])) {
1525       $relDirection = "_{$rel[1]}_{$rel[2]}";
1526     }
1527     else {
1528       // this call is coming from somewhere where the direction was resolved early on (e.g an api call)
1529       // so we can assume _a_b
1530       $relDirection = "_a_b";
1531       $targetContact = [$params['contact_id_b'] => 1];
1532     }
1533
1534     if (($action & CRM_Core_Action::ADD) ||
1535       ($action & CRM_Core_Action::DELETE)
1536     ) {
1537       $contact = $contactId;
1538     }
1539     elseif ($action & CRM_Core_Action::UPDATE) {
1540       $contact = (int) $ids['contact'];
1541       $targetContact = [$ids['contactTarget'] => 1];
1542     }
1543
1544     // Build the 'values' array for
1545     // 1. ContactA
1546     // 2. ContactB
1547     // This will allow us to check if either of the contacts in
1548     // relationship have active memberships.
1549
1550     $values = [];
1551
1552     // 1. ContactA
1553     $values[$contact] = [
1554       'relatedContacts' => $targetContact,
1555       'relationshipTypeId' => $relTypeId,
1556       'relationshipTypeDirection' => $relDirection,
1557     ];
1558     // 2. ContactB
1559     if (!empty($targetContact)) {
1560       foreach ($targetContact as $cid => $donCare) {
1561         $values[$cid] = [
1562           'relatedContacts' => [$contact => 1],
1563           'relationshipTypeId' => $relTypeId,
1564         ];
1565
1566         $relTypeParams = ['id' => $relTypeId];
1567         $relTypeValues = [];
1568         CRM_Contact_BAO_RelationshipType::retrieve($relTypeParams, $relTypeValues);
1569
1570         if (CRM_Utils_Array::value('name_a_b', $relTypeValues) == CRM_Utils_Array::value('name_b_a', $relTypeValues)) {
1571           $values[$cid]['relationshipTypeDirection'] = '_a_b';
1572         }
1573         else {
1574           $values[$cid]['relationshipTypeDirection'] = ($relDirection == '_a_b') ? '_b_a' : '_a_b';
1575         }
1576       }
1577     }
1578
1579     $deceasedStatusId = array_search('Deceased', CRM_Member_PseudoConstant::membershipStatus());
1580
1581     $relationshipProcessor = new CRM_Member_Utils_RelationshipProcessor(array_keys($values), $active);
1582     foreach ($values as $cid => $details) {
1583       $relatedContacts = array_keys(CRM_Utils_Array::value('relatedContacts', $details, []));
1584       $mainRelatedContactId = reset($relatedContacts);
1585
1586       foreach ($relationshipProcessor->getRelationshipMembershipsForContact((int) $cid) as $membershipId => $membershipValues) {
1587         $membershipInherittedFromContactID = NULL;
1588         if (!empty($membershipValues['owner_membership_id'])) {
1589           // @todo - $membership already has this now.
1590           // Use get not getsingle so that we get e-notice noise but not a fatal is the membership has already been deleted.
1591           $inheritedFromMembership = civicrm_api3('Membership', 'get', ['id' => $membershipValues['owner_membership_id'], 'sequential' => 1])['values'][0];
1592           $membershipInherittedFromContactID = (int) $inheritedFromMembership['contact_id'];
1593         }
1594         $relTypeIds = [];
1595         if ($action & CRM_Core_Action::DELETE) {
1596           // @todo don't return relTypeId here - but it seems to be used later in a cryptic way (hint cryptic is not a complement).
1597           list($relTypeId, $isDeletable) = self::isInheritedMembershipInvalidated($membershipValues, $values, $cid);
1598           if ($isDeletable) {
1599             CRM_Member_BAO_Membership::deleteRelatedMemberships($membershipValues['owner_membership_id'], $membershipValues['contact_id']);
1600           }
1601           continue;
1602         }
1603         if (($action & CRM_Core_Action::UPDATE) &&
1604           ($status & self::PAST) &&
1605           ($membershipValues['owner_membership_id'])
1606         ) {
1607           // If relationship is PAST and action is UPDATE
1608           // then delete the RELATED membership
1609           CRM_Member_BAO_Membership::deleteRelatedMemberships($membershipValues['owner_membership_id'],
1610             $membershipValues['contact_id']
1611           );
1612           continue;
1613         }
1614
1615         // add / edit the memberships for related
1616         // contacts.
1617
1618         // @todo - all these lines get 'relTypeDirs' - but it's already a key in the $membership array.
1619         // Get the Membership Type Details.
1620         $membershipType = CRM_Member_BAO_MembershipType::getMembershipType($membershipValues['membership_type_id']);
1621         // Check if contact's relationship type exists in membership type
1622         $relTypeDirs = [];
1623         if (!empty($membershipType['relationship_type_id'])) {
1624           $relTypeIds = (array) $membershipType['relationship_type_id'];
1625         }
1626         if (!empty($membershipType['relationship_direction'])) {
1627           $relDirections = (array) $membershipType['relationship_direction'];
1628         }
1629         foreach ($relTypeIds as $key => $value) {
1630           $relTypeDirs[] = $value . '_' . $relDirections[$key];
1631         }
1632         $relTypeDir = $details['relationshipTypeId'] . $details['relationshipTypeDirection'];
1633         if (in_array($relTypeDir, $relTypeDirs)) {
1634           // Check if relationship being created/updated is
1635           // similar to that of membership type's
1636           // relationship.
1637
1638           $membershipValues['owner_membership_id'] = $membershipId;
1639           unset($membershipValues['id']);
1640           unset($membershipValues['contact_id']);
1641           unset($membershipValues['membership_id']);
1642           foreach ($details['relatedContacts'] as $relatedContactId => $donCare) {
1643             $membershipValues['contact_id'] = $relatedContactId;
1644             if ($deceasedStatusId &&
1645               CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $relatedContactId, 'is_deceased')
1646             ) {
1647               $membershipValues['status_id'] = $deceasedStatusId;
1648               $membershipValues['skipStatusCal'] = TRUE;
1649             }
1650
1651             if (in_array($action, [CRM_Core_Action::UPDATE, CRM_Core_Action::ADD, CRM_Core_Action::ENABLE])) {
1652               //if updated relationship is already related to contact don't delete existing inherited membership
1653               if (in_array((int) $relatedContactId, $membershipValues['inheriting_contact_ids'], TRUE)
1654                 || $relatedContactId === $membershipValues['owner_contact_id']
1655               ) {
1656                 continue;
1657               }
1658
1659               //delete the membership record for related
1660               //contact before creating new membership record.
1661               CRM_Member_BAO_Membership::deleteRelatedMemberships($membershipId, $relatedContactId);
1662             }
1663             //skip status calculation for pay later memberships.
1664             if ('Pending' === CRM_Core_PseudoConstant::getName('CRM_Member_BAO_Membership', 'status_id', $membershipValues['status_id'])) {
1665               $membershipValues['skipStatusCal'] = TRUE;
1666             }
1667             // As long as the membership itself was not created by inheritance from the same contact
1668             // that stands to inherit the membership we add an inherited membership.
1669             if ($membershipInherittedFromContactID !== (int) $membershipValues['contact_id']) {
1670               $membershipValues = self::addInheritedMembership($membershipValues);
1671             }
1672           }
1673         }
1674         elseif ($action & CRM_Core_Action::UPDATE) {
1675           // if action is update and updated relationship do
1676           // not match with the existing
1677           // membership=>relationship then we need to
1678           // change the status of the membership record to expired for
1679           // previous relationship -- CRM-12078.
1680           // CRM-16087 we need to pass ownerMembershipId to isRelatedMembershipExpired function
1681           if (empty($params['relationship_ids']) && !empty($params['id'])) {
1682             $relIds = [$params['id']];
1683           }
1684           else {
1685             $relIds = $params['relationship_ids'] ?? NULL;
1686           }
1687           if (self::isRelatedMembershipExpired($relTypeIds, $contactId, $mainRelatedContactId, $relTypeId,
1688               $relIds) && !empty($membershipValues['owner_membership_id']
1689             ) && !empty($values[$mainRelatedContactId]['memberships'][$membershipValues['owner_membership_id']])) {
1690             $membershipValues['status_id'] = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipStatus', 'Expired', 'id', 'label');
1691             $type = CRM_Core_DAO::getFieldValue('CRM_Member_DAO_MembershipType', $membershipValues['membership_type_id'], 'name', 'id');
1692             CRM_Member_BAO_Membership::add($membershipValues);
1693             CRM_Core_Session::setStatus(ts("Inherited membership %1 status was changed to Expired due to the change in relationship type.", [1 => $type]), ts('Record Updated'), 'alert');
1694           }
1695         }
1696       }
1697     }
1698   }
1699
1700   /**
1701    * Helper function to check whether the membership is expired or not.
1702    *
1703    * Function takes a list of related membership types and if it is not also passed a
1704    * relationship ID of that types evaluates whether the membership status should be changed to expired.
1705    *
1706    * @param array $membershipTypeRelationshipTypeIDs
1707    *   Relation type IDs related to the given membership type.
1708    * @param int $contactId
1709    * @param int $mainRelatedContactId
1710    * @param int $relTypeId
1711    * @param array $relIds
1712    *
1713    * @return bool
1714    */
1715   public static function isRelatedMembershipExpired($membershipTypeRelationshipTypeIDs, $contactId, $mainRelatedContactId, $relTypeId, $relIds) {
1716     if (empty($membershipTypeRelationshipTypeIDs) || in_array($relTypeId, $membershipTypeRelationshipTypeIDs)) {
1717       return FALSE;
1718     }
1719
1720     if (empty($relIds)) {
1721       return FALSE;
1722     }
1723
1724     $relParamas = [
1725       1 => [$contactId, 'Integer'],
1726       2 => [$mainRelatedContactId, 'Integer'],
1727     ];
1728
1729     if ($contactId == $mainRelatedContactId) {
1730       $recordsFound = (int) CRM_Core_DAO::singleValueQuery("SELECT COUNT(*) FROM civicrm_relationship WHERE relationship_type_id IN ( " . implode(',', $membershipTypeRelationshipTypeIDs) . " )  AND
1731 contact_id_a IN ( %1 ) OR contact_id_b IN ( %1 ) AND id IN (" . implode(',', $relIds) . ")", $relParamas);
1732       if ($recordsFound) {
1733         return FALSE;
1734       }
1735       return TRUE;
1736     }
1737
1738     $recordsFound = (int) CRM_Core_DAO::singleValueQuery("SELECT COUNT(*) FROM civicrm_relationship WHERE relationship_type_id IN ( " . implode(',', $membershipTypeRelationshipTypeIDs) . " ) AND contact_id_a IN ( %1, %2 ) AND contact_id_b IN ( %1, %2 ) AND id NOT IN (" . implode(',', $relIds) . ")", $relParamas);
1739
1740     if ($recordsFound) {
1741       return FALSE;
1742     }
1743
1744     return TRUE;
1745   }
1746
1747   /**
1748    * Get Current Employer for Contact.
1749    *
1750    * @param $contactIds
1751    *   Contact Ids.
1752    *
1753    * @return array
1754    *   array of the current employer
1755    */
1756   public static function getCurrentEmployer($contactIds) {
1757     $contacts = implode(',', $contactIds);
1758
1759     $query = "
1760 SELECT organization_name, id, employer_id
1761 FROM civicrm_contact
1762 WHERE id IN ( {$contacts} )
1763 ";
1764
1765     $dao = CRM_Core_DAO::executeQuery($query);
1766     $currentEmployer = [];
1767     while ($dao->fetch()) {
1768       $currentEmployer[$dao->id]['org_id'] = $dao->employer_id;
1769       $currentEmployer[$dao->id]['org_name'] = $dao->organization_name;
1770     }
1771
1772     return $currentEmployer;
1773   }
1774
1775   /**
1776    * Function to return list of permissioned contacts for a given contact and relationship type.
1777    *
1778    * @param int $contactID
1779    *   contact id whose permissioned contacts are to be found.
1780    * @param int $relTypeId
1781    *   one or more relationship type id's.
1782    * @param string $name
1783    * @param string $contactType
1784    *
1785    * @return array
1786    *   Array of contacts
1787    */
1788   public static function getPermissionedContacts($contactID, $relTypeId = NULL, $name = NULL, $contactType = NULL) {
1789     $contacts = [];
1790     $args = [1 => [$contactID, 'Integer']];
1791     $relationshipTypeClause = $contactTypeClause = '';
1792
1793     if ($relTypeId) {
1794       // @todo relTypeId is only ever passed in as an int. Change this to reflect that -
1795       // probably being overly conservative by not doing so but working on stable release.
1796       $relationshipTypeClause = 'AND cr.relationship_type_id IN (%2) ';
1797       $args[2] = [$relTypeId, 'String'];
1798     }
1799
1800     if ($contactType) {
1801       $contactTypeClause = ' AND cr.relationship_type_id = crt.id AND crt.contact_type_b = %3 ';
1802       $args[3] = [$contactType, 'String'];
1803     }
1804
1805     $query = "
1806 SELECT cc.id as id, cc.sort_name as name
1807 FROM civicrm_relationship cr, civicrm_contact cc, civicrm_relationship_type crt
1808 WHERE
1809 cr.contact_id_a         = %1 AND
1810 cr.is_permission_a_b    = 1 AND
1811 IF(cr.end_date IS NULL, 1, (DATEDIFF( CURDATE( ), cr.end_date ) <= 0)) AND
1812 cr.is_active = 1 AND
1813 cc.id = cr.contact_id_b AND
1814 cc.is_deleted = 0
1815 $relationshipTypeClause
1816 $contactTypeClause
1817 ";
1818
1819     if (!empty($name)) {
1820       $name = CRM_Utils_Type::escape($name, 'String');
1821       $query .= "
1822 AND cc.sort_name LIKE '%$name%'";
1823     }
1824
1825     $dao = CRM_Core_DAO::executeQuery($query, $args);
1826     while ($dao->fetch()) {
1827       $contacts[$dao->id] = [
1828         'name' => $dao->name,
1829         'value' => $dao->id,
1830       ];
1831     }
1832
1833     return $contacts;
1834   }
1835
1836   /**
1837    * Merge relationships from otherContact to mainContact.
1838    *
1839    * Called during contact merge operation
1840    *
1841    * @param int $mainId
1842    *   Contact id of main contact record.
1843    * @param int $otherId
1844    *   Contact id of record which is going to merge.
1845    * @param array $sqls
1846    *   (reference) array of sql statements to append to.
1847    *
1848    * @see CRM_Dedupe_Merger::cpTables()
1849    */
1850   public static function mergeRelationships($mainId, $otherId, &$sqls) {
1851     // Delete circular relationships
1852     $sqls[] = "DELETE FROM civicrm_relationship
1853       WHERE (contact_id_a = $mainId AND contact_id_b = $otherId AND case_id IS NULL)
1854          OR (contact_id_b = $mainId AND contact_id_a = $otherId AND case_id IS NULL)";
1855
1856     // Delete relationship from other contact if main contact already has that relationship
1857     $sqls[] = "DELETE r2
1858       FROM civicrm_relationship r1, civicrm_relationship r2
1859       WHERE r1.relationship_type_id = r2.relationship_type_id
1860       AND r1.id <> r2.id
1861       AND r1.case_id IS NULL AND r2.case_id IS NULL
1862       AND (
1863         r1.contact_id_a = $mainId AND r2.contact_id_a = $otherId AND r1.contact_id_b = r2.contact_id_b
1864         OR r1.contact_id_b = $mainId AND r2.contact_id_b = $otherId AND r1.contact_id_a = r2.contact_id_a
1865         OR (
1866           (r1.contact_id_a = $mainId AND r2.contact_id_b = $otherId AND r1.contact_id_b = r2.contact_id_a
1867           OR r1.contact_id_b = $mainId AND r2.contact_id_a = $otherId AND r1.contact_id_a = r2.contact_id_b)
1868           AND r1.relationship_type_id IN (SELECT id FROM civicrm_relationship_type WHERE name_b_a = name_a_b)
1869         )
1870       )";
1871
1872     // Move relationships
1873     $sqls[] = "UPDATE IGNORE civicrm_relationship SET contact_id_a = $mainId WHERE contact_id_a = $otherId";
1874     $sqls[] = "UPDATE IGNORE civicrm_relationship SET contact_id_b = $mainId WHERE contact_id_b = $otherId";
1875
1876     // Move current employer id (name will get updated later)
1877     $sqls[] = "UPDATE civicrm_contact SET employer_id = $mainId WHERE employer_id = $otherId";
1878   }
1879
1880   /**
1881    * Set 'is_valid' field to false for all relationships whose end date is in the past, ie. are expired.
1882    *
1883    * @return bool
1884    *   True on success, false if error is encountered.
1885    * @throws \CiviCRM_API3_Exception
1886    */
1887   public static function disableExpiredRelationships() {
1888     $query = "SELECT id FROM civicrm_relationship WHERE is_active = 1 AND end_date < CURDATE()";
1889
1890     $dao = CRM_Core_DAO::executeQuery($query);
1891     while ($dao->fetch()) {
1892       $result = CRM_Contact_BAO_Relationship::setIsActive($dao->id, FALSE);
1893       // Result will be NULL if error occurred. We abort early if error detected.
1894       if ($result == NULL) {
1895         return FALSE;
1896       }
1897     }
1898     return TRUE;
1899   }
1900
1901   /**
1902    * Function filters the query by possible relationships for the membership type.
1903    *
1904    * It is intended to be called when constructing queries for the api (reciprocal & non-reciprocal)
1905    * and to add clauses to limit the return to those relationships which COULD inherit a membership type
1906    * (as opposed to those who inherit a particular membership
1907    *
1908    * @param array $params
1909    *   Api input array.
1910    * @param null $direction
1911    *
1912    * @return array|void
1913    * @throws \CiviCRM_API3_Exception
1914    */
1915   public static function membershipTypeToRelationshipTypes(&$params, $direction = NULL) {
1916     $membershipType = civicrm_api3('membership_type', 'getsingle', [
1917       'id' => $params['membership_type_id'],
1918       'return' => 'relationship_type_id, relationship_direction',
1919     ]);
1920     $relationshipTypes = $membershipType['relationship_type_id'];
1921     if (empty($relationshipTypes)) {
1922       return NULL;
1923     }
1924     // if we don't have any contact data we can only filter on type
1925     if (empty($params['contact_id']) && empty($params['contact_id_a']) && empty($params['contact_id_a'])) {
1926       $params['relationship_type_id'] = ['IN' => $relationshipTypes];
1927       return NULL;
1928     }
1929     else {
1930       $relationshipDirections = (array) $membershipType['relationship_direction'];
1931       // if we have contact_id_a OR contact_id_b we can make a call here
1932       // if we have contact??
1933       foreach ($relationshipDirections as $index => $mtdirection) {
1934         if (isset($params['contact_id_a']) && $mtdirection == 'a_b' || $direction == 'a_b') {
1935           $types[] = $relationshipTypes[$index];
1936         }
1937         if (isset($params['contact_id_b']) && $mtdirection == 'b_a' || $direction == 'b_a') {
1938           $types[] = $relationshipTypes[$index];
1939         }
1940       }
1941       if (!empty($types)) {
1942         $params['relationship_type_id'] = ['IN' => $types];
1943       }
1944       elseif (!empty($clauses)) {
1945         return explode(' OR ', $clauses);
1946       }
1947       else {
1948         // effectively setting it to return no results
1949         $params['relationship_type_id'] = 0;
1950       }
1951     }
1952   }
1953
1954   /**
1955    * Wrapper for contact relationship selector.
1956    *
1957    * @param array $params
1958    *   Associated array for params record id.
1959    *
1960    * @return array
1961    *   associated array of contact relationships
1962    * @throws \Exception
1963    */
1964   public static function getContactRelationshipSelector(&$params) {
1965     // format the params
1966     $params['offset'] = ($params['page'] - 1) * $params['rp'];
1967     $params['sort'] = $params['sortBy'] ?? NULL;
1968
1969     if ($params['context'] == 'past') {
1970       $relationshipStatus = CRM_Contact_BAO_Relationship::INACTIVE;
1971     }
1972     elseif ($params['context'] == 'all') {
1973       $relationshipStatus = CRM_Contact_BAO_Relationship::ALL;
1974     }
1975     else {
1976       $relationshipStatus = CRM_Contact_BAO_Relationship::CURRENT;
1977     }
1978
1979     // check logged in user for permission
1980     $page = new CRM_Core_Page();
1981     CRM_Contact_Page_View::checkUserPermission($page, $params['contact_id']);
1982     $permissions = [$page->_permission];
1983     if ($page->_permission == CRM_Core_Permission::EDIT) {
1984       $permissions[] = CRM_Core_Permission::DELETE;
1985     }
1986     $mask = CRM_Core_Action::mask($permissions);
1987
1988     $permissionedContacts = TRUE;
1989     if ($params['context'] != 'user') {
1990       $links = CRM_Contact_Page_View_Relationship::links();
1991     }
1992     else {
1993       $links = CRM_Contact_Page_View_UserDashBoard::links();
1994       $mask = NULL;
1995     }
1996     // get contact relationships
1997     $relationships = CRM_Contact_BAO_Relationship::getRelationship($params['contact_id'],
1998       $relationshipStatus,
1999       $params['rp'], 0, 0,
2000       $links, $mask,
2001       $permissionedContacts,
2002       $params, TRUE
2003     );
2004
2005     $contactRelationships = [];
2006     $params['total'] = $relationships['total_relationships'];
2007     unset($relationships['total_relationships']);
2008     if (!empty($relationships)) {
2009
2010       $displayName = CRM_Contact_BAO_Contact::displayName($params['contact_id']);
2011
2012       // format params
2013       foreach ($relationships as $relationshipId => $values) {
2014         $relationship = [];
2015
2016         $relationship['DT_RowId'] = $values['id'];
2017         $relationship['DT_RowClass'] = 'crm-entity';
2018         if ($values['is_active'] == 0) {
2019           $relationship['DT_RowClass'] .= ' disabled';
2020         }
2021
2022         $relationship['DT_RowAttr'] = [];
2023         $relationship['DT_RowAttr']['data-entity'] = 'relationship';
2024         $relationship['DT_RowAttr']['data-id'] = $values['id'];
2025
2026         //Add image icon for related contacts: CRM-14919; CRM-19668
2027         $contactType = (!empty($values['contact_sub_type'])) ? $values['contact_sub_type'] : $values['contact_type'];
2028         $icon = CRM_Contact_BAO_Contact_Utils::getImage($contactType,
2029           FALSE,
2030           $values['cid']
2031         );
2032         $relationship['sort_name'] = $icon . ' ' . CRM_Utils_System::href(
2033             $values['name'],
2034             'civicrm/contact/view',
2035             "reset=1&cid={$values['cid']}");
2036
2037         $relationship['relation'] = CRM_Utils_Array::value('case', $values, '') . CRM_Utils_System::href(
2038             $values['relation'],
2039             'civicrm/contact/view/rel',
2040             "action=view&reset=1&cid={$values['cid']}&id={$values['id']}&rtype={$values['rtype']}");
2041
2042         if (!empty($values['description'])) {
2043           $relationship['relation'] .= "<p class='description'>{$values['description']}</p>";
2044         }
2045
2046         if ($params['context'] == 'current') {
2047           $smarty = CRM_Core_Smarty::singleton();
2048
2049           $contactCombos = [
2050             [
2051               'permContact' => $params['contact_id'],
2052               'permDisplayName' => $displayName,
2053               'otherContact' => $values['cid'],
2054               'otherDisplayName' => $values['display_name'],
2055               'columnKey' => 'sort_name',
2056             ],
2057             [
2058               'permContact' => $values['cid'],
2059               'permDisplayName' => $values['display_name'],
2060               'otherContact' => $params['contact_id'],
2061               'otherDisplayName' => $displayName,
2062               'columnKey' => 'relation',
2063             ],
2064           ];
2065
2066           foreach ($contactCombos as $combo) {
2067             foreach ([CRM_Contact_BAO_Relationship::EDIT, CRM_Contact_BAO_Relationship::VIEW] as $permType) {
2068               $smarty->assign('permType', $permType);
2069               if (($combo['permContact'] == $values['contact_id_a'] and $values['is_permission_a_b'] == $permType)
2070                 || ($combo['permContact'] == $values['contact_id_b'] and $values['is_permission_b_a'] == $permType)
2071               ) {
2072                 $smarty->assign('permDisplayName', $combo['permDisplayName']);
2073                 $smarty->assign('otherDisplayName', $combo['otherDisplayName']);
2074                 $relationship[$combo['columnKey']] .= $smarty->fetch('CRM/Contact/Page/View/RelationshipPerm.tpl');
2075               }
2076             }
2077           }
2078         }
2079
2080         $relationship['start_date'] = CRM_Utils_Date::customFormat($values['start_date']);
2081         $relationship['end_date'] = CRM_Utils_Date::customFormat($values['end_date']);
2082         $relationship['city'] = $values['city'];
2083         $relationship['state'] = $values['state'];
2084         $relationship['email'] = $values['email'];
2085         $relationship['phone'] = $values['phone'];
2086         $relationship['links'] = $values['action'];
2087
2088         array_push($contactRelationships, $relationship);
2089       }
2090     }
2091
2092     $columnHeaders = self::getColumnHeaders();
2093     $selector = NULL;
2094     CRM_Utils_Hook::searchColumns('relationship.rows', $columnHeaders, $contactRelationships, $selector);
2095
2096     $relationshipsDT = [];
2097     $relationshipsDT['data'] = $contactRelationships;
2098     $relationshipsDT['recordsTotal'] = $params['total'];
2099     $relationshipsDT['recordsFiltered'] = $params['total'];
2100
2101     return $relationshipsDT;
2102   }
2103
2104   /**
2105    * @return array
2106    */
2107   public static function getColumnHeaders() {
2108     return [
2109       'relation' => [
2110         'name' => ts('Relationship'),
2111         'sort' => 'relation',
2112         'direction' => CRM_Utils_Sort::ASCENDING,
2113       ],
2114       'sort_name' => [
2115         'name' => '',
2116         'sort' => 'sort_name',
2117         'direction' => CRM_Utils_Sort::ASCENDING,
2118       ],
2119       'start_date' => [
2120         'name' => ts('Start'),
2121         'sort' => 'start_date',
2122         'direction' => CRM_Utils_Sort::DONTCARE,
2123       ],
2124       'end_date' => [
2125         'name' => ts('End'),
2126         'sort' => 'end_date',
2127         'direction' => CRM_Utils_Sort::DONTCARE,
2128       ],
2129       'city' => [
2130         'name' => ts('City'),
2131         'sort' => 'city',
2132         'direction' => CRM_Utils_Sort::DONTCARE,
2133       ],
2134       'state' => [
2135         'name' => ts('State/Prov'),
2136         'sort' => 'state',
2137         'direction' => CRM_Utils_Sort::DONTCARE,
2138       ],
2139       'email' => [
2140         'name' => ts('Email'),
2141         'sort' => 'email',
2142         'direction' => CRM_Utils_Sort::DONTCARE,
2143       ],
2144       'phone' => [
2145         'name' => ts('Phone'),
2146         'sort' => 'phone',
2147         'direction' => CRM_Utils_Sort::DONTCARE,
2148       ],
2149       'links' => [
2150         'name' => '',
2151         'sort' => 'links',
2152         'direction' => CRM_Utils_Sort::DONTCARE,
2153       ],
2154     ];
2155   }
2156
2157   /**
2158    * @inheritdoc
2159    */
2160   public static function buildOptions($fieldName, $context = NULL, $props = []) {
2161     // Quickform-specific format, for use when editing relationship type options in a popup from the contact relationship form
2162     if ($fieldName === 'relationship_type_id' && !empty($props['is_form'])) {
2163       return self::getContactRelationshipType(
2164         $props['contact_id'] ?? NULL,
2165         $props['relationship_direction'] ?? 'a_b',
2166         $props['relationship_id'] ?? NULL,
2167         $props['contact_type'] ?? NULL
2168       );
2169     }
2170
2171     return parent::buildOptions($fieldName, $context, $props);
2172   }
2173
2174   /**
2175    * Process the params from api, form and check if current
2176    * employer should be set or unset.
2177    *
2178    * @param array $params
2179    * @param int $relationshipId
2180    * @param int|null $updatedRelTypeID
2181    *
2182    * @return bool
2183    *   TRUE if current employer needs to be cleared.
2184    * @throws \CiviCRM_API3_Exception
2185    */
2186   public static function isCurrentEmployerNeedingToBeCleared($params, $relationshipId, $updatedRelTypeID = NULL) {
2187     $existingTypeID = (int) CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Relationship', $relationshipId, 'relationship_type_id');
2188     $updatedRelTypeID = $updatedRelTypeID ? $updatedRelTypeID : $existingTypeID;
2189     $currentEmployerID = (int) civicrm_api3('Contact', 'getvalue', ['return' => 'current_employer_id', 'id' => $params['contact_id_a']]);
2190
2191     if ($currentEmployerID !== (int) $params['contact_id_b'] || !self::isRelationshipTypeCurrentEmployer($existingTypeID)) {
2192       return FALSE;
2193     }
2194     //Clear employer if relationship is expired.
2195     if (!empty($params['end_date']) && strtotime($params['end_date']) < time()) {
2196       return TRUE;
2197     }
2198     //current employer checkbox is disabled on the form.
2199     //inactive or relationship type(employer of) is updated.
2200     if ((isset($params['is_current_employer']) && empty($params['is_current_employer']))
2201       || ((isset($params['is_active']) && empty($params['is_active'])))
2202       || $existingTypeID != $updatedRelTypeID) {
2203       // If there are no other active employer relationships between the same 2 contacts...
2204       if (!civicrm_api3('Relationship', 'getcount', [
2205         'is_active' => 1,
2206         'relationship_type_id' => $existingTypeID,
2207         'id' => ['<>' => $params['id']],
2208         'contact_id_a' => $params['contact_id_a'],
2209         'contact_id_b' => $params['contact_id_b'],
2210       ])) {
2211         return TRUE;
2212       }
2213     }
2214
2215     return FALSE;
2216   }
2217
2218   /**
2219    * Is this a current employer relationship type.
2220    *
2221    * @todo - this could use cached pseudoconstant lookups.
2222    *
2223    * @param int $existingTypeID
2224    *
2225    * @return bool
2226    */
2227   private static function isRelationshipTypeCurrentEmployer(int $existingTypeID): bool {
2228     $isCurrentEmployerRelationshipType = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_RelationshipType', $existingTypeID, 'name_b_a') === 'Employer of';
2229     return $isCurrentEmployerRelationshipType;
2230   }
2231
2232   /**
2233    * Is the inherited relationship invalidated by this relationship change.
2234    *
2235    * @param $membershipValues
2236    * @param array $values
2237    * @param int $cid
2238    *
2239    * @return array
2240    * @throws \CiviCRM_API3_Exception
2241    */
2242   private static function isInheritedMembershipInvalidated($membershipValues, array $values, $cid): array {
2243     // @todo most of this can go - it's just the weird historical returning of $relTypeId that it does.
2244     // now we have caching the parent fn can just call CRM_Member_BAO_MembershipType::getMembershipType
2245     $membershipType = CRM_Member_BAO_MembershipType::getMembershipType($membershipValues['membership_type_id']);
2246     $relTypeIds = $membershipType['relationship_type_id'];
2247     $membershipInheritedFrom = $membershipValues['owner_membership_id'] ?? NULL;
2248     if (!$membershipInheritedFrom || !in_array($values[$cid]['relationshipTypeId'], $relTypeIds)) {
2249       return [implode(',', $relTypeIds), FALSE];
2250     }
2251     //CRM-16300 check if owner membership exist for related membership
2252     return [implode(',', $relTypeIds), !self::isContactHasValidRelationshipToInheritMembershipType((int) $cid, (int) $membershipValues['membership_type_id'], (int) $membershipValues['owner_membership_id'])];
2253   }
2254
2255   /**
2256    * Is there a valid relationship confering this membership type on this contact.
2257    *
2258    * @param int $contactID
2259    * @param int $membershipTypeID
2260    * @param int $parentMembershipID
2261    *   Id of the membership being inherited.
2262    *
2263    * @return bool
2264    *
2265    * @throws \CiviCRM_API3_Exception
2266    */
2267   private static function isContactHasValidRelationshipToInheritMembershipType(int $contactID, int $membershipTypeID, int $parentMembershipID): bool {
2268     $membershipType = CRM_Member_BAO_MembershipType::getMembershipType($membershipTypeID);
2269     $existingRelationships = civicrm_api3('Relationship', 'get', [
2270       'contact_id_a' => $contactID,
2271       'contact_id_b' => $contactID,
2272       'relationship_type_id' => ['IN' => $membershipType['relationship_type_id']],
2273       'options' => ['or' => [['contact_id_a', 'contact_id_b']], 'limit' => 0],
2274       'is_active' => 1,
2275     ])['values'];
2276
2277     if (empty($existingRelationships)) {
2278       return FALSE;
2279     }
2280
2281     $membershipInheritedFromContactID = (int) civicrm_api3('Membership', 'getvalue', ['return' => 'contact_id', 'id' => $parentMembershipID]);
2282     // I don't think the api can correctly filter by start & end because of handling for NULL
2283     // so we filter them out here.
2284     foreach ($existingRelationships as $index => $existingRelationship) {
2285       $otherContactID = (int) (($contactID === (int) $existingRelationship['contact_id_a']) ? $existingRelationship['contact_id_b'] : $existingRelationship['contact_id_a']);
2286       if (!empty($existingRelationship['start_date'])
2287         && strtotime($existingRelationship['start_date']) > time()
2288       ) {
2289         unset($existingRelationships[$index]);
2290         continue;
2291       }
2292       if (!empty($existingRelationship['end_date'])
2293         && strtotime($existingRelationship['end_date']) < time()
2294       ) {
2295         unset($existingRelationships[$index]);
2296         continue;
2297       }
2298       if ($membershipInheritedFromContactID !== $otherContactID
2299       ) {
2300         // This is a weird scenario - they have been inheriting the  membership
2301         // just not from this relationship - and some max_related calcs etc would be required
2302         // - ie because they are no longer inheriting from this relationship's 'allowance'
2303         // and now are inheriting from the other relationships  'allowance', if it has not
2304         // already hit 'max_related'
2305         // For now ignore here & hope it's handled elsewhere - at least that's consistent with
2306         // before this function was added.
2307         unset($existingRelationships[$index]);
2308         continue;
2309       }
2310       if (!civicrm_api3('Contact', 'getcount', ['id' => $otherContactID, 'is_deleted' => 0])) {
2311         // Can't inherit from a deleted contact.
2312         unset($existingRelationships[$index]);
2313         continue;
2314       }
2315     }
2316     return !empty($existingRelationships);
2317   }
2318
2319   /**
2320    * Add an inherited membership, provided max related not exceeded.
2321    *
2322    * @param array $membershipValues
2323    *
2324    * @return array
2325    * @throws \CRM_Core_Exception
2326    */
2327   protected static function addInheritedMembership($membershipValues) {
2328     $query = "
2329 SELECT count(*)
2330   FROM civicrm_membership
2331     LEFT JOIN civicrm_membership_status ON (civicrm_membership_status.id = civicrm_membership.status_id)
2332  WHERE membership_type_id = {$membershipValues['membership_type_id']}
2333            AND owner_membership_id = {$membershipValues['owner_membership_id']}
2334     AND is_current_member = 1";
2335     $result = CRM_Core_DAO::singleValueQuery($query);
2336     if ($result < CRM_Utils_Array::value('max_related', $membershipValues, PHP_INT_MAX)) {
2337       // Convert custom_xx_id fields to custom_xx
2338       // See https://lab.civicrm.org/dev/membership/-/issues/37
2339       // This makes sure the value is copied and not the looked up value.
2340       // Which is the case when the custom field is a contact reference field.
2341       // The custom_xx contains the display name of the contact, instead of the contact id.
2342       // The contact id is then available in custom_xx_id.
2343       foreach ($membershipValues as $field => $value) {
2344         if (stripos($field, 'custom_') !== 0) {
2345           // No a custom field
2346           continue;
2347         }
2348         $custom_id = substr($field, 7);
2349         if (substr($custom_id, -3) === '_id') {
2350           $custom_id = substr($custom_id, 0, -3);
2351           unset($membershipValues[$field]);
2352           $membershipValues['custom_' . $custom_id] = $value;
2353         }
2354       }
2355
2356       civicrm_api3('Membership', 'create', $membershipValues);
2357     }
2358     return $membershipValues;
2359   }
2360
2361 }