CRM/Report/Form/Contact/Relationship.php

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