CRM/Report/Form/Contact/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  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  * $Id$
  17  *
  18  */
  19 class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form {
  20
  21   protected $_summary = NULL;
  22   protected $_emailField_a = FALSE;
  23   protected $_emailField_b = FALSE;
  24   protected $_phoneField_a = FALSE;
  25   protected $_phoneField_b = FALSE;
  26   protected $_customGroupExtends = [
  27     'Relationship',
  28   ];
  29   public $_drilldownReport = ['contact/detail' => 'Link to Detail Report'];
  30
  31   /**
  32    * This report has not been optimised for group filtering.
  33    *
  34    * The functionality for group filtering has been improved but not
  35    * all reports have been adjusted to take care of it. This report has not
  36    * and will run an inefficient query until fixed.
  37    *
  38    * CRM-19170
  39    *
  40    * @var bool
  41    */
  42   protected $groupFilterNotOptimised = TRUE;
  43
  44   /**
  45    * This will be a_b or b_a.
  46    *
  47    * @var string
  48    */
  49   protected $relationType;
  50
  51   /**
  52    * Class constructor.
  53    */
  54   public function __construct() {
  55
  56     $contact_type = CRM_Contact_BAO_ContactType::getSelectElements(FALSE, TRUE, '_');
  57
  58     $this->_columns = [
  59       'civicrm_contact' => [
  60         'dao' => 'CRM_Contact_DAO_Contact',
  61         'fields' => [
  62           'sort_name_a' => [
  63             'title' => ts('Contact A'),
  64             'name' => 'sort_name',
  65             'required' => TRUE,
  66           ],
  67           'display_name_a' => [
  68             'title' => ts('Contact A Full Name'),
  69             'name' => 'display_name',
  70           ],
  71           'id' => [
  72             'no_display' => TRUE,
  73             'required' => TRUE,
  74           ],
  75           'contact_type_a' => [
  76             'title' => ts('Contact Type (Contact A)'),
  77             'name' => 'contact_type',
  78           ],
  79           'contact_sub_type_a' => [
  80             'title' => ts('Contact Subtype (Contact A)'),
  81             'name' => 'contact_sub_type',
  82           ],
  83         ],
  84         'filters' => [
  85           'sort_name_a' => [
  86             'title' => ts('Contact A'),
  87             'name' => 'sort_name',
  88             'operator' => 'like',
  89             'type' => CRM_Report_Form::OP_STRING,
  90           ],
  91           'contact_type_a' => [
  92             'title' => ts('Contact Type A'),
  93             'name' => 'contact_type',
  94             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  95             'options' => $contact_type,
  96             'type' => CRM_Utils_Type::T_STRING,
  97           ],
  98         ],
  99         'order_bys' => [
 100           'sort_name_a' => [
 101             'title' => ts('Contact A'),
 102             'name' => 'sort_name',
 103             'default_weight' => '1',
 104           ],
 105         ],
 106         'grouping' => 'contact_a_fields',
 107       ],
 108       'civicrm_contact_b' => [
 109         'dao' => 'CRM_Contact_DAO_Contact',
 110         'alias' => 'contact_b',
 111         'fields' => [
 112           'sort_name_b' => [
 113             'title' => ts('Contact B'),
 114             'name' => 'sort_name',
 115             'required' => TRUE,
 116           ],
 117           'display_name_b' => [
 118             'title' => ts('Contact B Full Name'),
 119             'name' => 'display_name',
 120           ],
 121           'id' => [
 122             'no_display' => TRUE,
 123             'required' => TRUE,
 124           ],
 125           'contact_type_b' => [
 126             'title' => ts('Contact Type (Contact B)'),
 127             'name' => 'contact_type',
 128           ],
 129           'contact_sub_type_b' => [
 130             'title' => ts('Contact Subtype (Contact B)'),
 131             'name' => 'contact_sub_type',
 132           ],
 133         ],
 134         'filters' => [
 135           'sort_name_b' => [
 136             'title' => ts('Contact B'),
 137             'name' => 'sort_name',
 138             'operator' => 'like',
 139             'type' => CRM_Report_Form::OP_STRING,
 140           ],
 141           'contact_type_b' => [
 142             'title' => ts('Contact Type B'),
 143             'name' => 'contact_type',
 144             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 145             'options' => $contact_type,
 146             'type' => CRM_Utils_Type::T_STRING,
 147           ],
 148         ],
 149         'order_bys' => [
 150           'sort_name_b' => [
 151             'title' => ts('Contact B'),
 152             'name' => 'sort_name',
 153             'default_weight' => '2',
 154           ],
 155         ],
 156         'grouping' => 'contact_b_fields',
 157       ],
 158       'civicrm_email' => [
 159         'dao' => 'CRM_Core_DAO_Email',
 160         'fields' => [
 161           'email_a' => [
 162             'title' => ts('Email (Contact A)'),
 163             'name' => 'email',
 164           ],
 165         ],
 166         'grouping' => 'contact_a_fields',
 167       ],
 168       'civicrm_email_b' => [
 169         'dao' => 'CRM_Core_DAO_Email',
 170         'alias' => 'email_b',
 171         'fields' => [
 172           'email_b' => [
 173             'title' => ts('Email (Contact B)'),
 174             'name' => 'email',
 175           ],
 176         ],
 177         'grouping' => 'contact_b_fields',
 178       ],
 179       'civicrm_phone' => [
 180         'dao' => 'CRM_Core_DAO_Phone',
 181         'alias' => 'phone_a',
 182         'fields' => [
 183           'phone_a' => [
 184             'title' => ts('Phone (Contact A)'),
 185             'name' => 'phone',
 186           ],
 187           'phone_ext_a' => [
 188             'title' => ts('Phone Ext (Contact A)'),
 189             'name' => 'phone_ext',
 190           ],
 191         ],
 192         'grouping' => 'contact_a_fields',
 193       ],
 194       'civicrm_phone_b' => [
 195         'dao' => 'CRM_Core_DAO_Phone',
 196         'alias' => 'phone_b',
 197         'fields' => [
 198           'phone_b' => [
 199             'title' => ts('Phone (Contact B)'),
 200             'name' => 'phone',
 201           ],
 202           'phone_ext_b' => [
 203             'title' => ts('Phone Ext (Contact B)'),
 204             'name' => 'phone_ext',
 205           ],
 206         ],
 207         'grouping' => 'contact_b_fields',
 208       ],
 209       'civicrm_relationship_type' => [
 210         'dao' => 'CRM_Contact_DAO_RelationshipType',
 211         'fields' => [
 212           'label_a_b' => [
 213             'title' => ts('Relationship A-B '),
 214             'default' => TRUE,
 215           ],
 216           'label_b_a' => [
 217             'title' => ts('Relationship B-A '),
 218             'default' => TRUE,
 219           ],
 220         ],
 221         'order_bys' => [
 222           'label_a_b' => [
 223             'title' => ts('Relationship A-B'),
 224             'name' => 'label_a_b',
 225           ],
 226           'label_b_A' => [
 227             'title' => ts('Relationship B-A'),
 228             'name' => 'label_b_a',
 229           ],
 230         ],
 231         'grouping' => 'relation-fields',
 232       ],
 233       'civicrm_relationship' => [
 234         'dao' => 'CRM_Contact_DAO_Relationship',
 235         'fields' => [
 236           'start_date' => [
 237             'title' => ts('Relationship Start Date'),
 238           ],
 239           'end_date' => [
 240             'title' => ts('Relationship End Date'),
 241           ],
 242           'is_permission_a_b' => [
 243             'title' => ts('Permission A has to access B'),
 244           ],
 245           'is_permission_b_a' => [
 246             'title' => ts('Permission B has to access A'),
 247           ],
 248           'description' => [
 249             'title' => ts('Description'),
 250           ],
 251           'is_active' => [
 252             'title' => ts('Is active?'),
 253             'type' => CRM_Utils_Type::T_BOOLEAN,
 254           ],
 255           'relationship_id' => [
 256             'title' => ts('Rel ID'),
 257             'name' => 'id',
 258           ],
 259         ],
 260         'filters' => [
 261           'is_active' => [
 262             'title' => ts('Relationship Status'),
 263             'operatorType' => CRM_Report_Form::OP_SELECT,
 264             'options' => [
 265               '' => ts('- Any -'),
 266               1 => ts('Active'),
 267               0 => ts('Inactive'),
 268             ],
 269             'type' => CRM_Utils_Type::T_INT,
 270           ],
 271           'is_valid' => [
 272             'title' => ts('Relationship Dates Validity'),
 273             'operatorType' => CRM_Report_Form::OP_SELECT,
 274             'options' => [
 275               NULL => ts('- Any -'),
 276               1 => ts('Not expired'),
 277               0 => ts('Expired'),
 278             ],
 279             'type' => CRM_Utils_Type::T_INT,
 280           ],
 281           'relationship_type_id' => [
 282             'title' => ts('Relationship'),
 283             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 284             'options' => CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE),
 285             'type' => CRM_Utils_Type::T_INT,
 286           ],
 287           'start_date' => [
 288             'title' => ts('Start Date'),
 289             'type' => CRM_Utils_Type::T_DATE,
 290           ],
 291           'end_date' => [
 292             'title' => ts('End Date'),
 293             'type' => CRM_Utils_Type::T_DATE,
 294           ],
 295           'active_period_date' => [
 296             'title' => ts('Active Period'),
 297             'type' => CRM_Utils_Type::T_DATE,
 298           ],
 299           'is_permission_a_b' => [
 300             'title' => ts('Does contact A have permission over contact B?'),
 301             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 302             'options' => CRM_Contact_BAO_Relationship::buildOptions('is_permission_a_b'),
 303             'type' => CRM_Utils_Type::T_INT,
 304           ],
 305           'is_permission_b_a' => [
 306             'title' => ts('Does contact B have permission over contact A?'),
 307             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 308             'options' => CRM_Contact_BAO_Relationship::buildOptions('is_permission_b_a'),
 309             'type' => CRM_Utils_Type::T_INT,
 310           ],
 311         ],
 312
 313         'order_bys' => [
 314           'start_date' => [
 315             'title' => ts('Start Date'),
 316             'name' => 'start_date',
 317           ],
 318           'end_date' => [
 319             'title' => ts('End Date'),
 320             'name' => 'end_date',
 321           ],
 322         ],
 323         'grouping' => 'relation-fields',
 324       ],
 325       'civicrm_address' => [
 326         'dao' => 'CRM_Core_DAO_Address',
 327         'filters' => [
 328           'country_id' => [
 329             'title' => ts('Country'),
 330             'type' => CRM_Utils_Type::T_INT,
 331             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 332             'options' => CRM_Core_PseudoConstant::country(),
 333           ],
 334           'state_province_id' => [
 335             'title' => ts('State/Province'),
 336             'type' => CRM_Utils_Type::T_INT,
 337             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 338             'options' => CRM_Core_PseudoConstant::stateProvince(),
 339           ],
 340         ],
 341         'grouping' => 'contact-fields',
 342       ],
 343     ];
 344
 345     $this->_groupFilter = TRUE;
 346     $this->_tagFilter = TRUE;
 347     parent::__construct();
 348   }
 349
 350   public function preProcess() {
 351     parent::preProcess();
 352   }
 353
 354   public function select() {
 355     $select = $this->_columnHeaders = [];
 356     foreach ($this->_columns as $tableName => $table) {
 357       if (array_key_exists('fields', $table)) {
 358         foreach ($table['fields'] as $fieldName => $field) {
 359           if (!empty($field['required']) ||
 360             !empty($this->_params['fields'][$fieldName])
 361           ) {
 362
 363             if ($fieldName == 'email_a') {
 364               $this->_emailField_a = TRUE;
 365             }
 366             if ($fieldName == 'email_b') {
 367               $this->_emailField_b = TRUE;
 368             }
 369             if ($fieldName == 'phone_a') {
 370               $this->_phoneField_a = TRUE;
 371             }
 372             if ($fieldName == 'phone_b') {
 373               $this->_phoneField_b = TRUE;
 374             }
 375             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 376             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = $field['type'] ?? NULL;
 377             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'] ?? NULL;
 378           }
 379         }
 380       }
 381     }
 382     $this->_selectClauses = $select;
 383
 384     $this->_select = "SELECT " . implode(', ', $select) . " ";
 385   }
 386
 387   public function from() {
 388     $this->_from = "
 389         FROM civicrm_relationship {$this->_aliases['civicrm_relationship']}
 390
 391              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 392                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a =
 393                              {$this->_aliases['civicrm_contact']}.id )
 394
 395              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact_b']}
 396                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_b =
 397                              {$this->_aliases['civicrm_contact_b']}.id )
 398
 399              {$this->_aclFrom} ";
 400
 401     if (!empty($this->_params['country_id_value']) ||
 402       !empty($this->_params['state_province_id_value'])
 403     ) {
 404       $this->_from .= "
 405             INNER  JOIN civicrm_address {$this->_aliases['civicrm_address']}
 406                          ON (( {$this->_aliases['civicrm_address']}.contact_id =
 407                                {$this->_aliases['civicrm_contact']}.id  OR
 408                                {$this->_aliases['civicrm_address']}.contact_id =
 409                                {$this->_aliases['civicrm_contact_b']}.id ) AND
 410                                {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 411     }
 412
 413     $this->_from .= "
 414         INNER JOIN civicrm_relationship_type {$this->_aliases['civicrm_relationship_type']}
 415                         ON ( {$this->_aliases['civicrm_relationship']}.relationship_type_id  =
 416                              {$this->_aliases['civicrm_relationship_type']}.id  ) ";
 417
 418     // Include Email Field.
 419     if ($this->_emailField_a) {
 420       $this->_from .= "
 421              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 422                        ON ( {$this->_aliases['civicrm_contact']}.id =
 423                             {$this->_aliases['civicrm_email']}.contact_id AND
 424                             {$this->_aliases['civicrm_email']}.is_primary = 1 )";
 425     }
 426     if ($this->_emailField_b) {
 427       $this->_from .= "
 428              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email_b']}
 429                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 430                             {$this->_aliases['civicrm_email_b']}.contact_id AND
 431                             {$this->_aliases['civicrm_email_b']}.is_primary = 1 )";
 432     }
 433     // Include Phone Field.
 434     if ($this->_phoneField_a) {
 435       $this->_from .= "
 436              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 437                        ON ( {$this->_aliases['civicrm_contact']}.id =
 438                             {$this->_aliases['civicrm_phone']}.contact_id AND
 439                             {$this->_aliases['civicrm_phone']}.is_primary = 1 )";
 440     }
 441     if ($this->_phoneField_b) {
 442       $this->_from .= "
 443              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone_b']}
 444                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 445                             {$this->_aliases['civicrm_phone_b']}.contact_id AND
 446                             {$this->_aliases['civicrm_phone_b']}.is_primary = 1 )";
 447     }
 448   }
 449
 450   public function where() {
 451     $whereClauses = $havingClauses = [];
 452     foreach ($this->_columns as $tableName => $table) {
 453       if (array_key_exists('filters', $table)) {
 454         foreach ($table['filters'] as $fieldName => $field) {
 455
 456           $clause = NULL;
 457           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 458             $relative = $this->_params["{$fieldName}_relative"] ?? NULL;
 459             $from = $this->_params["{$fieldName}_from"] ?? NULL;
 460             $to = $this->_params["{$fieldName}_to"] ?? NULL;
 461
 462             if ($fieldName == 'active_period_date') {
 463               $clause = $this->activeClause($field['name'], $relative, $from, $to, $field['type']);
 464             }
 465             else {
 466               $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 467             }
 468           }
 469           else {
 470             $op = $this->_params["{$fieldName}_op"] ?? NULL;
 471             if ($op) {
 472               if (($tableName == 'civicrm_contact' ||
 473                   $tableName == 'civicrm_contact_b') &&
 474                 ($fieldName == 'contact_type_a' ||
 475                   $fieldName == 'contact_type_b')
 476               ) {
 477                 $cTypes = $this->_params["{$fieldName}_value"] ?? NULL;
 478                 $contactTypes = $contactSubTypes = [];
 479                 if (!empty($cTypes)) {
 480                   foreach ($cTypes as $ctype) {
 481                     $getTypes = CRM_Utils_System::explode('_', $ctype, 2);
 482                     if ($getTypes[1] &&
 483                       !in_array($getTypes[1], $contactSubTypes)
 484                     ) {
 485                       $contactSubTypes[] = $getTypes[1];
 486                     }
 487                     elseif ($getTypes[0] &&
 488                       !in_array($getTypes[0], $contactTypes)
 489                     ) {
 490                       $contactTypes[] = $getTypes[0];
 491                     }
 492                   }
 493                 }
 494
 495                 if (!empty($contactTypes)) {
 496                   $clause = $this->whereClause($field,
 497                     $op,
 498                     $contactTypes,
 499                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 500                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 501                   );
 502                 }
 503
 504                 if (!empty($contactSubTypes)) {
 505                   $field['name'] = 'contact_sub_type';
 506                   $field['dbAlias'] = $field['alias'] . '.' . $field['name'];
 507                   $subTypeClause = $this->whereClause($field,
 508                     $op,
 509                     $contactSubTypes,
 510                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 511                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 512                   );
 513                   if ($clause) {
 514                     $clause = '(' . $clause . ' OR ' . $subTypeClause . ')';
 515                   }
 516                   else {
 517                     $clause = $subTypeClause;
 518                   }
 519                 }
 520               }
 521               else {
 522                 if ($fieldName == 'is_valid') {
 523                   $clause = $this->buildValidityQuery(CRM_Utils_Array::value("{$fieldName}_value", $this->_params));
 524                 }
 525                 else {
 526                   $clause = $this->whereClause($field,
 527                     $op,
 528                     CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 529                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 530                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 531                   );
 532                 }
 533               }
 534             }
 535           }
 536
 537           if (!empty($clause)) {
 538             if (!empty($field['having'])) {
 539               $havingClauses[] = $clause;
 540             }
 541             else {
 542               $whereClauses[] = $clause;
 543             }
 544           }
 545         }
 546       }
 547     }
 548     $this->_where = "WHERE ( {$this->_aliases['civicrm_contact']}.is_deleted = 0 AND {$this->_aliases['civicrm_contact_b']}.is_deleted = 0 ) ";
 549     if ($whereClauses) {
 550       $this->_where .= ' AND ' . implode(' AND ', $whereClauses);
 551     }
 552     else {
 553       $this->_having = '';
 554     }
 555
 556     if ($this->_aclWhere) {
 557       $this->_where .= " AND {$this->_aclWhere} ";
 558     }
 559
 560     if (!empty($havingClauses)) {
 561       // use this clause to construct group by clause.
 562       $this->_having = 'HAVING ' . implode(' AND ', $havingClauses);
 563     }
 564   }
 565
 566   /**
 567    * @param $rows
 568    *
 569    * @return array
 570    */
 571   public function statistics(&$rows) {
 572     $statistics = parent::statistics($rows);
 573
 574     $isStatusFilter = FALSE;
 575     $relStatus = NULL;
 576     if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') {
 577       $relStatus = ts('Is equal to Active');
 578     }
 579     elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') {
 580       $relStatus = ts('Is equal to Inactive');
 581     }
 582     if (!empty($statistics['filters'])) {
 583       foreach ($statistics['filters'] as $id => $value) {
 584         // For displaying relationship type filter.
 585         if ($value['title'] == 'Relationship') {
 586           $relTypes = CRM_Core_PseudoConstant::relationshipType();
 587           $op = CRM_Utils_Array::value('relationship_type_id_op', $this->_params) == 'in' ? ts('Is one of') . ' ' : ts('Is not one of') . ' ';
 588           $relationshipTypes = [];
 589           foreach ($this->_params['relationship_type_id_value'] as $relationship) {
 590             $relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType];
 591           }
 592           $statistics['filters'][$id]['value'] = $op .
 593             implode(', ', $relationshipTypes);
 594         }
 595
 596         // For displaying relationship status.
 597         if ($value['title'] == 'Relationship Status') {
 598           $isStatusFilter = TRUE;
 599           $statistics['filters'][$id]['value'] = $relStatus;
 600         }
 601       }
 602     }
 603     // For displaying relationship status.
 604     if (!$isStatusFilter && $relStatus) {
 605       $statistics['filters'][] = [
 606         'title' => ts('Relationship Status'),
 607         'value' => $relStatus,
 608       ];
 609     }
 610     return $statistics;
 611   }
 612
 613   public function groupBy() {
 614     $this->_groupBy = " ";
 615     $groupBy = [];
 616     if ($this->relationType == 'a_b') {
 617       $groupBy[] = " {$this->_aliases['civicrm_contact']}.id";
 618     }
 619     elseif ($this->relationType == 'b_a') {
 620       $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id";
 621     }
 622
 623     if (!empty($groupBy)) {
 624       $groupBy[] = "{$this->_aliases['civicrm_relationship']}.id";
 625     }
 626     else {
 627       $groupBy = ["{$this->_aliases['civicrm_relationship']}.id"];
 628     }
 629
 630     $this->_groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($this->_selectClauses, $groupBy);
 631   }
 632
 633   public function beginPostProcessCommon() {
 634     $originalRelationshipTypeIdValue = $this->_params['relationship_type_id_value'] ?? NULL;
 635     if ($originalRelationshipTypeIdValue) {
 636       $relationshipTypes = [];
 637       $direction = [];
 638       foreach ((array) $originalRelationshipTypeIdValue as $relationship_type) {
 639         $relType = explode('_', $relationship_type);
 640         $direction[] = $relType[1] . '_' . $relType[2];
 641         $relationshipTypes[] = intval($relType[0]);
 642       }
 643       // Lets take the first relationship type to guide us in the relationship
 644       // direction we should use.
 645       $this->relationType = $direction[0];
 646       $this->_params['relationship_type_id_value'] = $relationshipTypes;
 647     }
 648   }
 649
 650   public function postProcess() {
 651     $this->beginPostProcess();
 652
 653     $this->buildACLClause([
 654       $this->_aliases['civicrm_contact'],
 655       $this->_aliases['civicrm_contact_b'],
 656     ]);
 657
 658     $sql = $this->buildQuery();
 659     $this->buildRows($sql, $rows);
 660
 661     $this->formatDisplay($rows);
 662     $this->doTemplateAssignment($rows);
 663
 664     if (!empty($originalRelationshipTypeIdValue)) {
 665       // Store its old value, CRM-5837.
 666       $this->_params['relationship_type_id_value'] = $originalRelationshipTypeIdValue;
 667     }
 668     $this->endPostProcess($rows);
 669   }
 670
 671   /**
 672    * @param $rows
 673    */
 674   public function alterDisplay(&$rows) {
 675     // Custom code to alter rows.
 676     $entryFound = FALSE;
 677
 678     foreach ($rows as $rowNum => $row) {
 679
 680       // Handle ID to label conversion for contact fields
 681       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, 'contact/relationship', 'View Relationships') ? TRUE : $entryFound;
 682
 683       // Handle contact subtype A
 684       // @todo refactor into separate function
 685       if (array_key_exists('civicrm_contact_contact_sub_type_a', $row)) {
 686         if ($value = $row['civicrm_contact_contact_sub_type_a']) {
 687           $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 688           $rowLabels = [];
 689           foreach ($rowValues as $rowValue) {
 690             if ($rowValue) {
 691               $rowLabels[] = CRM_Core_PseudoConstant::getLabel('CRM_Contact_BAO_Contact', 'contact_sub_type', $rowValue);
 692             }
 693           }
 694           $rows[$rowNum]['civicrm_contact_contact_sub_type_a'] = implode(', ', $rowLabels);
 695         }
 696         $entryFound = TRUE;
 697       }
 698
 699       // Handle contact subtype B
 700       // @todo refactor into separate function
 701       if (array_key_exists('civicrm_contact_b_contact_sub_type_b', $row)) {
 702         if ($value = $row['civicrm_contact_b_contact_sub_type_b']) {
 703           $rowValues = explode(CRM_Core_DAO::VALUE_SEPARATOR, $value);
 704           $rowLabels = [];
 705           foreach ($rowValues as $rowValue) {
 706             if ($rowValue) {
 707               $rowLabels[] = CRM_Core_PseudoConstant::getLabel('CRM_Contact_BAO_Contact', 'contact_sub_type', $rowValue);
 708             }
 709           }
 710           $rows[$rowNum]['civicrm_contact_b_contact_sub_type_b'] = implode(', ', $rowLabels);
 711         }
 712         $entryFound = TRUE;
 713       }
 714
 715       $entryFound = $this->alterDisplayAddressFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 716
 717       // Handle contact name A
 718       // @todo refactor into separate function
 719       if (array_key_exists('civicrm_contact_sort_name_a', $row) &&
 720         array_key_exists('civicrm_contact_id', $row)
 721       ) {
 722         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 723           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 724           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 725         );
 726         $rows[$rowNum]['civicrm_contact_sort_name_a']
 727           = $rows[$rowNum]['civicrm_contact_sort_name_a'] . ' (' .
 728           $rows[$rowNum]['civicrm_contact_id'] . ')';
 729         $rows[$rowNum]['civicrm_contact_sort_name_a_link'] = $url;
 730         $rows[$rowNum]['civicrm_contact_sort_name_a_hover'] = ts('View Contact Detail Report for this contact');
 731         $entryFound = TRUE;
 732       }
 733
 734       // Handle contact name B
 735       // @todo refactor into separate function
 736       if (array_key_exists('civicrm_contact_b_sort_name_b', $row) &&
 737         array_key_exists('civicrm_contact_b_id', $row)
 738       ) {
 739         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 740           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_b_id'],
 741           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 742         );
 743         $rows[$rowNum]['civicrm_contact_b_sort_name_b']
 744           = $rows[$rowNum]['civicrm_contact_b_sort_name_b'] . ' (' .
 745           $rows[$rowNum]['civicrm_contact_b_id'] . ')';
 746         $rows[$rowNum]['civicrm_contact_b_sort_name_b_link'] = $url;
 747         $rows[$rowNum]['civicrm_contact_b_sort_name_b_hover'] = ts('View Contact Detail Report for this contact');
 748         $entryFound = TRUE;
 749       }
 750
 751       // Handle relationship
 752       if (array_key_exists('civicrm_relationship_relationship_id', $row) &&
 753         array_key_exists('civicrm_contact_id', $row)
 754       ) {
 755         $url = "/civicrm/contact/view/rel?reset=1&action=update&rtype=a_b&cid=" .
 756           $row['civicrm_contact_id'] . "&id=" .
 757           $row['civicrm_relationship_relationship_id'];
 758         $rows[$rowNum]['civicrm_relationship_relationship_id_link'] = $url;
 759         $rows[$rowNum]['civicrm_relationship_relationship_id_hover'] = ts("Edit this relationship.");
 760         $entryFound = TRUE;
 761       }
 762
 763       // Handle permissioned relationships
 764       if (array_key_exists('civicrm_relationship_is_permission_a_b', $row)) {
 765         $rows[$rowNum]['civicrm_relationship_is_permission_a_b']
 766           = ts(self::permissionedRelationship($row['civicrm_relationship_is_permission_a_b']));
 767         $entryFound = TRUE;
 768       }
 769
 770       if (array_key_exists('civicrm_relationship_is_permission_b_a', $row)) {
 771         $rows[$rowNum]['civicrm_relationship_is_permission_b_a']
 772           = ts(self::permissionedRelationship($row['civicrm_relationship_is_permission_b_a']));
 773         $entryFound = TRUE;
 774       }
 775
 776       // skip looking further in rows, if first row itself doesn't
 777       // have the column we need
 778       if (!$entryFound) {
 779         break;
 780       }
 781     }
 782   }
 783
 784   /**
 785    * Convert values to permissioned relationship descriptions
 786    * @param  [int] $key
 787    * @return [string]
 788    */
 789   public static function permissionedRelationship($key) {
 790     static $lookup;
 791     if (!$lookup) {
 792       $lookup = CRM_Contact_BAO_Relationship::buildOptions("is_permission_a_b");
 793     };
 794     return $lookup[$key] ?? NULL;
 795   }
 796
 797   /**
 798    * @param $valid bool - set to 1 if we are looking for a valid relationship, 0 if not
 799    *
 800    * @return array
 801    */
 802   public function buildValidityQuery($valid) {
 803     $clause = NULL;
 804     if ($valid == '1') {
 805       // Relationships dates are not expired.
 806       $clause = "((start_date <= CURDATE() OR start_date is null) AND (end_date >= CURDATE() OR end_date is null))";
 807     }
 808     elseif ($valid == '0') {
 809       // Relationships dates are expired or has not started yet.
 810       $clause = "(start_date >= CURDATE() OR end_date < CURDATE())";
 811     }
 812     return $clause;
 813   }
 814
 815   /**
 816    * Get SQL where clause for a active period field.
 817    *
 818    * @param string $fieldName
 819    * @param string $relative
 820    * @param string $from
 821    * @param string $to
 822    * @param string $type
 823    *
 824    * @return null|string
 825    */
 826   public function activeClause(
 827     $fieldName,
 828     $relative, $from, $to, $type = NULL) {
 829     $clauses = [];
 830     if (in_array($relative, array_keys($this->getOperationPair(CRM_Report_Form::OP_DATE)))) {
 831       return NULL;
 832     }
 833
 834     list($from, $to) = $this->getFromTo($relative, $from, $to);
 835
 836     if ($from) {
 837       $from = ($type == CRM_Utils_Type::T_DATE) ? substr($from, 0, 8) : $from;
 838     }
 839
 840     if ($to) {
 841       $to = ($type == CRM_Utils_Type::T_DATE) ? substr($to, 0, 8) : $to;
 842     }
 843
 844     if ($from || $to) {
 845       return CRM_Contact_BAO_Query::getRelationshipActivePeriodClauses($from, $to, FALSE);
 846     }
 847     return NULL;
 848   }
 849
 850 }