CRM/Report/Form/Contact/Relationship.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  10  | CiviCRM is free software; you can copy, modify, and distribute it  |
  11  | under the terms of the GNU Affero General Public License           |
  12  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13  |                                                                    |
  14  | CiviCRM is distributed in the hope that it will be useful, but     |
  15  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17  | See the GNU Affero General Public License for more details.        |
  18  |                                                                    |
  19  | You should have received a copy of the GNU Affero General Public   |
  20  | License and the CiviCRM Licensing Exception along                  |
  21  | with this program; if not, contact CiviCRM LLC                     |
  22  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23  | GNU Affero General Public License or the licensing of CiviCRM,     |
  24  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2015
  32  * $Id$
  33  *
  34  */
  35 class CRM_Report_Form_Contact_Relationship extends CRM_Report_Form {
  36
  37   protected $_summary = NULL;
  38   protected $_emailField_a = FALSE;
  39   protected $_emailField_b = FALSE;
  40   protected $_phoneField_a = FALSE;
  41   protected $_phoneField_b = FALSE;
  42   protected $_customGroupExtends = array(
  43     'Relationship',
  44   );
  45   public $_drilldownReport = array('contact/detail' => 'Link to Detail Report');
  46
  47   /**
  48    * This will be a_b or b_a.
  49    *
  50    * @var string
  51    */
  52   protected $relationType;
  53
  54   /**
  55    * Class constructor.
  56    */
  57   public function __construct() {
  58
  59     $contact_type = CRM_Contact_BAO_ContactType::getSelectElements(FALSE, TRUE, '_');
  60
  61     $this->_columns = array(
  62       'civicrm_contact' => array(
  63         'dao' => 'CRM_Contact_DAO_Contact',
  64         'fields' => array(
  65           'sort_name_a' => array(
  66             'title' => ts('Contact A'),
  67             'name' => 'sort_name',
  68             'required' => TRUE,
  69           ),
  70           'id' => array(
  71             'no_display' => TRUE,
  72             'required' => TRUE,
  73           ),
  74           'contact_type_a' => array(
  75             'title' => ts('Contact Type (Contact A)'),
  76             'name' => 'contact_type',
  77           ),
  78           'contact_sub_type_a' => array(
  79             'title' => ts('Contact Subtype (Contact A)'),
  80             'name' => 'contact_sub_type',
  81           ),
  82         ),
  83         'filters' => array(
  84           'sort_name_a' => array(
  85             'title' => ts('Contact A'),
  86             'name' => 'sort_name',
  87             'operator' => 'like',
  88             'type' => CRM_Report_Form::OP_STRING,
  89           ),
  90           'contact_type_a' => array(
  91             'title' => ts('Contact Type A'),
  92             'name' => 'contact_type',
  93             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
  94             'options' => $contact_type,
  95             'type' => CRM_Utils_Type::T_STRING,
  96           ),
  97         ),
  98         'grouping' => 'contact_a_fields',
  99       ),
 100       'civicrm_contact_b' => array(
 101         'dao' => 'CRM_Contact_DAO_Contact',
 102         'alias' => 'contact_b',
 103         'fields' => array(
 104           'sort_name_b' => array(
 105             'title' => ts('Contact B'),
 106             'name' => 'sort_name',
 107             'required' => TRUE,
 108           ),
 109           'id' => array(
 110             'no_display' => TRUE,
 111             'required' => TRUE,
 112           ),
 113           'contact_type_b' => array(
 114             'title' => ts('Contact Type (Contact B)'),
 115             'name' => 'contact_type',
 116           ),
 117           'contact_sub_type_b' => array(
 118             'title' => ts('Contact Subtype (Contact B)'),
 119             'name' => 'contact_sub_type',
 120           ),
 121         ),
 122         'filters' => array(
 123           'sort_name_b' => array(
 124             'title' => ts('Contact B'),
 125             'name' => 'sort_name',
 126             'operator' => 'like',
 127             'type' => CRM_Report_Form::OP_STRING,
 128           ),
 129           'contact_type_b' => array(
 130             'title' => ts('Contact Type B'),
 131             'name' => 'contact_type',
 132             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 133             'options' => $contact_type,
 134             'type' => CRM_Utils_Type::T_STRING,
 135           ),
 136         ),
 137         'grouping' => 'contact_b_fields',
 138       ),
 139       'civicrm_email' => array(
 140         'dao' => 'CRM_Core_DAO_Email',
 141         'fields' => array(
 142           'email_a' => array(
 143             'title' => ts('Email (Contact A)'),
 144             'name' => 'email',
 145           ),
 146         ),
 147         'grouping' => 'contact_a_fields',
 148       ),
 149       'civicrm_email_b' => array(
 150         'dao' => 'CRM_Core_DAO_Email',
 151         'alias' => 'email_b',
 152         'fields' => array(
 153           'email_b' => array(
 154             'title' => ts('Email (Contact B)'),
 155             'name' => 'email',
 156           ),
 157         ),
 158         'grouping' => 'contact_b_fields',
 159       ),
 160       'civicrm_phone' => array(
 161         'dao' => 'CRM_Core_DAO_Phone',
 162         'alias' => 'phone_a',
 163         'fields' => array(
 164           'phone_a' => array(
 165             'title' => ts('Phone (Contact A)'),
 166             'name' => 'phone',
 167           ),
 168           'phone_ext_a' => array(
 169             'title' => ts('Phone Ext (Contact A)'),
 170             'name' => 'phone_ext',
 171           ),
 172         ),
 173         'grouping' => 'contact_a_fields',
 174       ),
 175       'civicrm_phone_b' => array(
 176         'dao' => 'CRM_Core_DAO_Phone',
 177         'alias' => 'phone_b',
 178         'fields' => array(
 179           'phone_b' => array(
 180             'title' => ts('Phone (Contact B)'),
 181             'name' => 'phone',
 182           ),
 183           'phone_ext_b' => array(
 184             'title' => ts('Phone Ext (Contact B)'),
 185             'name' => 'phone_ext',
 186           ),
 187         ),
 188         'grouping' => 'contact_b_fields',
 189       ),
 190       'civicrm_relationship_type' => array(
 191         'dao' => 'CRM_Contact_DAO_RelationshipType',
 192         'fields' => array(
 193           'label_a_b' => array(
 194             'title' => ts('Relationship A-B '),
 195             'default' => TRUE,
 196           ),
 197           'label_b_a' => array(
 198             'title' => ts('Relationship B-A '),
 199             'default' => TRUE,
 200           ),
 201         ),
 202         'grouping' => 'relation-fields',
 203       ),
 204       'civicrm_relationship' => array(
 205         'dao' => 'CRM_Contact_DAO_Relationship',
 206         'fields' => array(
 207           'start_date' => array(
 208             'title' => ts('Relationship Start Date'),
 209           ),
 210           'end_date' => array(
 211             'title' => ts('Relationship End Date'),
 212           ),
 213           'description' => array(
 214             'title' => ts('Description'),
 215           ),
 216           'relationship_id' => array(
 217             'title' => ts('Rel ID'),
 218             'name' => 'id',
 219           ),
 220         ),
 221         'filters' => array(
 222           'is_active' => array(
 223             'title' => ts('Relationship Status'),
 224             'operatorType' => CRM_Report_Form::OP_SELECT,
 225             'options' => array(
 226               '' => '- Any -',
 227               1 => 'Active',
 228               0 => 'Inactive',
 229             ),
 230             'type' => CRM_Utils_Type::T_INT,
 231           ),
 232           'is_valid' => array(
 233             'title' => ts('Relationship Dates Validity'),
 234             'operatorType' => CRM_Report_Form::OP_SELECT,
 235             'options' => array(
 236               NULL => ts('- Any -'),
 237               1 => ts('Not expired'),
 238               0 => ts('Expired'),
 239             ),
 240             'type' => CRM_Utils_Type::T_INT,
 241           ),
 242           'relationship_type_id' => array(
 243             'title' => ts('Relationship'),
 244             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 245             'options' => CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, 'null', NULL, NULL, TRUE),
 246             'type' => CRM_Utils_Type::T_INT,
 247           ),
 248           'start_date' => array(
 249             'title' => ts('Start Date'),
 250             'type' => CRM_Utils_Type::T_DATE,
 251           ),
 252           'end_date' => array(
 253             'title' => ts('End Date'),
 254             'type' => CRM_Utils_Type::T_DATE,
 255           ),
 256         ),
 257         'grouping' => 'relation-fields',
 258       ),
 259       'civicrm_address' => array(
 260         'dao' => 'CRM_Core_DAO_Address',
 261         'filters' => array(
 262           'country_id' => array(
 263             'title' => ts('Country'),
 264             'type' => CRM_Utils_Type::T_INT,
 265             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 266             'options' => CRM_Core_PseudoConstant::country(),
 267           ),
 268           'state_province_id' => array(
 269             'title' => ts('State/Province'),
 270             'type' => CRM_Utils_Type::T_INT,
 271             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 272             'options' => CRM_Core_PseudoConstant::stateProvince(),
 273           ),
 274         ),
 275         'grouping' => 'contact-fields',
 276       ),
 277     );
 278
 279     $this->_groupFilter = TRUE;
 280     $this->_tagFilter = TRUE;
 281     parent::__construct();
 282   }
 283
 284   public function preProcess() {
 285     parent::preProcess();
 286   }
 287
 288   public function select() {
 289     $select = $this->_columnHeaders = array();
 290     foreach ($this->_columns as $tableName => $table) {
 291       if (array_key_exists('fields', $table)) {
 292         foreach ($table['fields'] as $fieldName => $field) {
 293           if (!empty($field['required']) ||
 294             !empty($this->_params['fields'][$fieldName])
 295           ) {
 296
 297             if ($fieldName == 'email_a') {
 298               $this->_emailField_a = TRUE;
 299             }
 300             if ($fieldName == 'email_b') {
 301               $this->_emailField_b = TRUE;
 302             }
 303             if ($fieldName == 'phone_a') {
 304               $this->_phoneField_a = TRUE;
 305             }
 306             if ($fieldName == 'phone_b') {
 307               $this->_phoneField_b = TRUE;
 308             }
 309             $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 310             $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 311             $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 312           }
 313         }
 314       }
 315     }
 316
 317     $this->_select = "SELECT " . implode(', ', $select) . " ";
 318   }
 319
 320   public function from() {
 321     $this->_from = "
 322         FROM civicrm_relationship {$this->_aliases['civicrm_relationship']}
 323
 324              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 325                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a =
 326                              {$this->_aliases['civicrm_contact']}.id )
 327
 328              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact_b']}
 329                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_b =
 330                              {$this->_aliases['civicrm_contact_b']}.id )
 331
 332              {$this->_aclFrom} ";
 333
 334     if (!empty($this->_params['country_id_value']) ||
 335       !empty($this->_params['state_province_id_value'])
 336     ) {
 337       $this->_from .= "
 338             INNER  JOIN civicrm_address {$this->_aliases['civicrm_address']}
 339                          ON (( {$this->_aliases['civicrm_address']}.contact_id =
 340                                {$this->_aliases['civicrm_contact']}.id  OR
 341                                {$this->_aliases['civicrm_address']}.contact_id =
 342                                {$this->_aliases['civicrm_contact_b']}.id ) AND
 343                                {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 344     }
 345
 346     $this->_from .= "
 347         INNER JOIN civicrm_relationship_type {$this->_aliases['civicrm_relationship_type']}
 348                         ON ( {$this->_aliases['civicrm_relationship']}.relationship_type_id  =
 349                              {$this->_aliases['civicrm_relationship_type']}.id  ) ";
 350
 351     // include Email Field
 352     if ($this->_emailField_a) {
 353       $this->_from .= "
 354              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 355                        ON ( {$this->_aliases['civicrm_contact']}.id =
 356                             {$this->_aliases['civicrm_email']}.contact_id AND
 357                             {$this->_aliases['civicrm_email']}.is_primary = 1 )";
 358     }
 359     if ($this->_emailField_b) {
 360       $this->_from .= "
 361              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email_b']}
 362                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 363                             {$this->_aliases['civicrm_email_b']}.contact_id AND
 364                             {$this->_aliases['civicrm_email_b']}.is_primary = 1 )";
 365     }
 366     // include Phone Field
 367     if ($this->_phoneField_a) {
 368       $this->_from .= "
 369              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 370                        ON ( {$this->_aliases['civicrm_contact']}.id =
 371                             {$this->_aliases['civicrm_phone']}.contact_id AND
 372                             {$this->_aliases['civicrm_phone']}.is_primary = 1 )";
 373     }
 374     if ($this->_phoneField_b) {
 375       $this->_from .= "
 376              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone_b']}
 377                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 378                             {$this->_aliases['civicrm_phone_b']}.contact_id AND
 379                             {$this->_aliases['civicrm_phone_b']}.is_primary = 1 )";
 380     }
 381   }
 382
 383   public function where() {
 384     $whereClauses = $havingClauses = array();
 385     foreach ($this->_columns as $tableName => $table) {
 386       if (array_key_exists('filters', $table)) {
 387         foreach ($table['filters'] as $fieldName => $field) {
 388
 389           $clause = NULL;
 390           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 391             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 392             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 393             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 394
 395             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 396           }
 397           else {
 398             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 399             if ($op) {
 400               if (($tableName == 'civicrm_contact' ||
 401                   $tableName == 'civicrm_contact_b') &&
 402                 ($fieldName == 'contact_type_a' ||
 403                   $fieldName == 'contact_type_b')
 404               ) {
 405                 $cTypes = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 406                 $contactTypes = $contactSubTypes = array();
 407                 if (!empty($cTypes)) {
 408                   foreach ($cTypes as $ctype) {
 409                     $getTypes = CRM_Utils_System::explode('_', $ctype, 2);
 410                     if ($getTypes[1] &&
 411                       !in_array($getTypes[1], $contactSubTypes)
 412                     ) {
 413                       $contactSubTypes[] = $getTypes[1];
 414                     }
 415                     elseif ($getTypes[0] &&
 416                       !in_array($getTypes[0], $contactTypes)
 417                     ) {
 418                       $contactTypes[] = $getTypes[0];
 419                     }
 420                   }
 421                 }
 422
 423                 if (!empty($contactTypes)) {
 424                   $clause = $this->whereClause($field,
 425                     $op,
 426                     $contactTypes,
 427                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 428                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 429                   );
 430                 }
 431
 432                 if (!empty($contactSubTypes)) {
 433                   $field['name'] = 'contact_sub_type';
 434                   $field['dbAlias'] = $field['alias'] . '.' . $field['name'];
 435                   $subTypeClause = $this->whereClause($field,
 436                     $op,
 437                     $contactSubTypes,
 438                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 439                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 440                   );
 441                   if ($clause) {
 442                     $clause = '(' . $clause . ' OR ' . $subTypeClause . ')';
 443                   }
 444                   else {
 445                     $clause = $subTypeClause;
 446                   }
 447                 }
 448               }
 449               else {
 450                 if ($fieldName == 'is_valid') {
 451                   $clause = $this->buildValidityQuery(CRM_Utils_Array::value("{$fieldName}_value", $this->_params));
 452                 }
 453                 else {
 454                   $clause = $this->whereClause($field,
 455                     $op,
 456                     CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 457                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 458                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 459                   );
 460                 }
 461               }
 462             }
 463           }
 464
 465           if (!empty($clause)) {
 466             if (!empty($field['having'])) {
 467               $havingClauses[] = $clause;
 468             }
 469             else {
 470               $whereClauses[] = $clause;
 471             }
 472           }
 473         }
 474       }
 475     }
 476
 477     if (empty($whereClauses)) {
 478       $this->_where = 'WHERE ( 1 ) ';
 479       $this->_having = '';
 480     }
 481     else {
 482       $this->_where = 'WHERE ' . implode(' AND ', $whereClauses);
 483     }
 484
 485     if ($this->_aclWhere) {
 486       $this->_where .= " AND {$this->_aclWhere} ";
 487     }
 488
 489     if (!empty($havingClauses)) {
 490       // use this clause to construct group by clause.
 491       $this->_having = 'HAVING ' . implode(' AND ', $havingClauses);
 492     }
 493   }
 494
 495   /**
 496    * @param $rows
 497    *
 498    * @return array
 499    */
 500   public function statistics(&$rows) {
 501     $statistics = parent::statistics($rows);
 502
 503     $isStatusFilter = FALSE;
 504     $relStatus = NULL;
 505     if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') {
 506       $relStatus = 'Is equal to Active';
 507     }
 508     elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') {
 509       $relStatus = 'Is equal to Inactive';
 510     }
 511     if (!empty($statistics['filters'])) {
 512       foreach ($statistics['filters'] as $id => $value) {
 513         //for displaying relationship type filter
 514         if ($value['title'] == 'Relationship') {
 515           $relTypes = CRM_Core_PseudoConstant::relationshipType();
 516           $op = CRM_Utils_array::value('relationship_type_id_op', $this->_params) == 'in' ?
 517             ts('Is one of') . ' ' : ts('Is not one of') . ' ';
 518           $relationshipTypes = array();
 519           foreach ($this->_params['relationship_type_id_value'] as $relationship) {
 520             $relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType];
 521           }
 522           $statistics['filters'][$id]['value'] = $op .
 523             implode(', ', $relationshipTypes);
 524         }
 525
 526         //for displaying relationship status
 527         if ($value['title'] == 'Relationship Status') {
 528           $isStatusFilter = TRUE;
 529           $statistics['filters'][$id]['value'] = $relStatus;
 530         }
 531       }
 532     }
 533     //for displaying relationship status
 534     if (!$isStatusFilter && $relStatus) {
 535       $statistics['filters'][] = array(
 536         'title' => 'Relationship Status',
 537         'value' => $relStatus,
 538       );
 539     }
 540     return $statistics;
 541   }
 542
 543   public function groupBy() {
 544     $this->_groupBy = " ";
 545     $groupBy = array();
 546     if ($this->relationType == 'a_b') {
 547       $groupBy[] = " {$this->_aliases['civicrm_contact']}.id";
 548     }
 549     elseif ($this->relationType == 'b_a') {
 550       $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id";
 551     }
 552
 553     if (!empty($groupBy)) {
 554       $this->_groupBy = " GROUP BY  " . implode(', ', $groupBy) .
 555         " ,  {$this->_aliases['civicrm_relationship']}.id ";
 556     }
 557     else {
 558       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_relationship']}.id ";
 559     }
 560   }
 561
 562   public function orderBy() {
 563     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact_b']}.sort_name ";
 564   }
 565
 566   public function postProcess() {
 567     $this->beginPostProcess();
 568
 569     $originalRelationshipTypeIdValue = $this->_params['relationship_type_id_value'];
 570     if (!empty($this->_params['relationship_type_id_value'])) {
 571       $relationshipTypes = array();
 572       $direction = array();
 573       $relType = array();
 574       foreach ($this->_params['relationship_type_id_value'] as $relationship_type) {
 575         $relType = explode('_', $relationship_type);
 576         $direction[] = $relType[1] . '_' . $relType[2];
 577         $relationshipTypes[] = intval($relType[0]);
 578       }
 579       // Lets take the first relationship type to guide us in the relationship direction
 580       // we should use.
 581       $this->relationType = $direction[0];
 582       $this->_params['relationship_type_id_value'] = $relationshipTypes;
 583     }
 584
 585     $this->buildACLClause(array(
 586       $this->_aliases['civicrm_contact'],
 587       $this->_aliases['civicrm_contact_b'],
 588     ));
 589     $sql = $this->buildQuery();
 590     $this->buildRows($sql, $rows);
 591
 592     $this->formatDisplay($rows);
 593     $this->doTemplateAssignment($rows);
 594
 595     if (!empty($originalRelationshipTypeIdValue)) {
 596       // store its old value, CRM-5837
 597       $this->_params['relationship_type_id_value'] = $originalRelationshipTypeIdValue;
 598     }
 599     $this->endPostProcess($rows);
 600   }
 601
 602   /**
 603    * @param $rows
 604    */
 605   public function alterDisplay(&$rows) {
 606     // custom code to alter rows
 607     $entryFound = FALSE;
 608
 609     foreach ($rows as $rowNum => $row) {
 610
 611       // handle country
 612       if (array_key_exists('civicrm_address_country_id', $row)) {
 613         if ($value = $row['civicrm_address_country_id']) {
 614           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 615         }
 616         $entryFound = TRUE;
 617       }
 618
 619       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 620         if ($value = $row['civicrm_address_state_province_id']) {
 621           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 622         }
 623         $entryFound = TRUE;
 624       }
 625
 626       if (array_key_exists('civicrm_contact_sort_name_a', $row) &&
 627         array_key_exists('civicrm_contact_id', $row)
 628       ) {
 629         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 630           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 631           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 632         );
 633         $rows[$rowNum]['civicrm_contact_sort_name_a']
 634           = $rows[$rowNum]['civicrm_contact_sort_name_a'] . ' (' .
 635           $rows[$rowNum]['civicrm_contact_id'] . ')';
 636         $rows[$rowNum]['civicrm_contact_sort_name_a_link'] = $url;
 637         $rows[$rowNum]['civicrm_contact_sort_name_a_hover'] = ts("View Contact details for this contact.");
 638         $entryFound = TRUE;
 639       }
 640
 641       if (array_key_exists('civicrm_contact_b_sort_name_b', $row) &&
 642         array_key_exists('civicrm_contact_b_id', $row)
 643       ) {
 644         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 645           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_b_id'],
 646           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 647         );
 648         $rows[$rowNum]['civicrm_contact_b_sort_name_b']
 649           = $rows[$rowNum]['civicrm_contact_b_sort_name_b'] . ' (' .
 650           $rows[$rowNum]['civicrm_contact_b_id'] . ')';
 651         $rows[$rowNum]['civicrm_contact_b_sort_name_b_link'] = $url;
 652         $rows[$rowNum]['civicrm_contact_b_sort_name_b_hover'] = ts("View Contact details for this contact.");
 653         $entryFound = TRUE;
 654       }
 655
 656       if (array_key_exists('civicrm_relationship_relationship_id', $row) &&
 657         array_key_exists('civicrm_contact_id', $row)
 658       ) {
 659         $url = "/civicrm/contact/view/rel?reset=1&action=update&rtype=a_b&cid=" .
 660           $row['civicrm_contact_id'] . "&id=" .
 661           $row['civicrm_relationship_relationship_id'];
 662         $rows[$rowNum]['civicrm_relationship_relationship_id_link'] = $url;
 663         $rows[$rowNum]['civicrm_relationship_relationship_id_hover'] = ts("Edit this relationship.");
 664         $entryFound = TRUE;
 665       }
 666
 667       // skip looking further in rows, if first row itself doesn't
 668       // have the column we need
 669       if (!$entryFound) {
 670         break;
 671       }
 672     }
 673   }
 674
 675   /**
 676    * @param $valid bool - set to 1 if we are looking for a valid relationship, 0 if not
 677    *
 678    * @return array
 679    */
 680   public function buildValidityQuery($valid) {
 681     $clause = NULL;
 682     if ($valid == '1') {
 683       // relationships dates are not expired
 684       $clause = "((start_date <= CURDATE() OR start_date is null) AND (end_date >= CURDATE() OR end_date is null))";
 685     }
 686     elseif ($valid == '0') {
 687       // relationships dates are expired or has not started yet
 688       $clause = "(start_date >= CURDATE() OR end_date < CURDATE())";
 689     }
 690     return $clause;
 691   }
 692
 693 }