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           '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
 325     $this->_select = "SELECT " . implode(', ', $select) . " ";
 326   }
 327
 328   public function from() {
 329     $this->_from = "
 330         FROM civicrm_relationship {$this->_aliases['civicrm_relationship']}
 331
 332              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
 333                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_a =
 334                              {$this->_aliases['civicrm_contact']}.id )
 335
 336              INNER JOIN civicrm_contact {$this->_aliases['civicrm_contact_b']}
 337                         ON ( {$this->_aliases['civicrm_relationship']}.contact_id_b =
 338                              {$this->_aliases['civicrm_contact_b']}.id )
 339
 340              {$this->_aclFrom} ";
 341
 342     if (!empty($this->_params['country_id_value']) ||
 343       !empty($this->_params['state_province_id_value'])
 344     ) {
 345       $this->_from .= "
 346             INNER  JOIN civicrm_address {$this->_aliases['civicrm_address']}
 347                          ON (( {$this->_aliases['civicrm_address']}.contact_id =
 348                                {$this->_aliases['civicrm_contact']}.id  OR
 349                                {$this->_aliases['civicrm_address']}.contact_id =
 350                                {$this->_aliases['civicrm_contact_b']}.id ) AND
 351                                {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 352     }
 353
 354     $this->_from .= "
 355         INNER JOIN civicrm_relationship_type {$this->_aliases['civicrm_relationship_type']}
 356                         ON ( {$this->_aliases['civicrm_relationship']}.relationship_type_id  =
 357                              {$this->_aliases['civicrm_relationship_type']}.id  ) ";
 358
 359     // include Email Field
 360     if ($this->_emailField_a) {
 361       $this->_from .= "
 362              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 363                        ON ( {$this->_aliases['civicrm_contact']}.id =
 364                             {$this->_aliases['civicrm_email']}.contact_id AND
 365                             {$this->_aliases['civicrm_email']}.is_primary = 1 )";
 366     }
 367     if ($this->_emailField_b) {
 368       $this->_from .= "
 369              LEFT JOIN civicrm_email {$this->_aliases['civicrm_email_b']}
 370                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 371                             {$this->_aliases['civicrm_email_b']}.contact_id AND
 372                             {$this->_aliases['civicrm_email_b']}.is_primary = 1 )";
 373     }
 374     // include Phone Field
 375     if ($this->_phoneField_a) {
 376       $this->_from .= "
 377              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 378                        ON ( {$this->_aliases['civicrm_contact']}.id =
 379                             {$this->_aliases['civicrm_phone']}.contact_id AND
 380                             {$this->_aliases['civicrm_phone']}.is_primary = 1 )";
 381     }
 382     if ($this->_phoneField_b) {
 383       $this->_from .= "
 384              LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone_b']}
 385                        ON ( {$this->_aliases['civicrm_contact_b']}.id =
 386                             {$this->_aliases['civicrm_phone_b']}.contact_id AND
 387                             {$this->_aliases['civicrm_phone_b']}.is_primary = 1 )";
 388     }
 389   }
 390
 391   public function where() {
 392     $whereClauses = $havingClauses = array();
 393     foreach ($this->_columns as $tableName => $table) {
 394       if (array_key_exists('filters', $table)) {
 395         foreach ($table['filters'] as $fieldName => $field) {
 396
 397           $clause = NULL;
 398           if (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 399             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 400             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 401             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 402
 403             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 404           }
 405           else {
 406             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 407             if ($op) {
 408               if (($tableName == 'civicrm_contact' ||
 409                   $tableName == 'civicrm_contact_b') &&
 410                 ($fieldName == 'contact_type_a' ||
 411                   $fieldName == 'contact_type_b')
 412               ) {
 413                 $cTypes = CRM_Utils_Array::value("{$fieldName}_value", $this->_params);
 414                 $contactTypes = $contactSubTypes = array();
 415                 if (!empty($cTypes)) {
 416                   foreach ($cTypes as $ctype) {
 417                     $getTypes = CRM_Utils_System::explode('_', $ctype, 2);
 418                     if ($getTypes[1] &&
 419                       !in_array($getTypes[1], $contactSubTypes)
 420                     ) {
 421                       $contactSubTypes[] = $getTypes[1];
 422                     }
 423                     elseif ($getTypes[0] &&
 424                       !in_array($getTypes[0], $contactTypes)
 425                     ) {
 426                       $contactTypes[] = $getTypes[0];
 427                     }
 428                   }
 429                 }
 430
 431                 if (!empty($contactTypes)) {
 432                   $clause = $this->whereClause($field,
 433                     $op,
 434                     $contactTypes,
 435                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 436                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 437                   );
 438                 }
 439
 440                 if (!empty($contactSubTypes)) {
 441                   $field['name'] = 'contact_sub_type';
 442                   $field['dbAlias'] = $field['alias'] . '.' . $field['name'];
 443                   $subTypeClause = $this->whereClause($field,
 444                     $op,
 445                     $contactSubTypes,
 446                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 447                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 448                   );
 449                   if ($clause) {
 450                     $clause = '(' . $clause . ' OR ' . $subTypeClause . ')';
 451                   }
 452                   else {
 453                     $clause = $subTypeClause;
 454                   }
 455                 }
 456               }
 457               else {
 458                 if ($fieldName == 'is_valid') {
 459                   $clause = $this->buildValidityQuery(CRM_Utils_Array::value("{$fieldName}_value", $this->_params));
 460                 }
 461                 else {
 462                   $clause = $this->whereClause($field,
 463                     $op,
 464                     CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 465                     CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 466                     CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 467                   );
 468                 }
 469               }
 470             }
 471           }
 472
 473           if (!empty($clause)) {
 474             if (!empty($field['having'])) {
 475               $havingClauses[] = $clause;
 476             }
 477             else {
 478               $whereClauses[] = $clause;
 479             }
 480           }
 481         }
 482       }
 483     }
 484
 485     if (empty($whereClauses)) {
 486       $this->_where = 'WHERE ( 1 ) ';
 487       $this->_having = '';
 488     }
 489     else {
 490       $this->_where = 'WHERE ' . implode(' AND ', $whereClauses);
 491     }
 492
 493     if ($this->_aclWhere) {
 494       $this->_where .= " AND {$this->_aclWhere} ";
 495     }
 496
 497     if (!empty($havingClauses)) {
 498       // use this clause to construct group by clause.
 499       $this->_having = 'HAVING ' . implode(' AND ', $havingClauses);
 500     }
 501   }
 502
 503   /**
 504    * @param $rows
 505    *
 506    * @return array
 507    */
 508   public function statistics(&$rows) {
 509     $statistics = parent::statistics($rows);
 510
 511     $isStatusFilter = FALSE;
 512     $relStatus = NULL;
 513     if (CRM_Utils_Array::value('is_active_value', $this->_params) == '1') {
 514       $relStatus = 'Is equal to Active';
 515     }
 516     elseif (CRM_Utils_Array::value('is_active_value', $this->_params) == '0') {
 517       $relStatus = 'Is equal to Inactive';
 518     }
 519     if (!empty($statistics['filters'])) {
 520       foreach ($statistics['filters'] as $id => $value) {
 521         //for displaying relationship type filter
 522         if ($value['title'] == 'Relationship') {
 523           $relTypes = CRM_Core_PseudoConstant::relationshipType();
 524           $op = CRM_Utils_array::value('relationship_type_id_op', $this->_params) == 'in' ?
 525             ts('Is one of') . ' ' : ts('Is not one of') . ' ';
 526           $relationshipTypes = array();
 527           foreach ($this->_params['relationship_type_id_value'] as $relationship) {
 528             $relationshipTypes[] = $relTypes[$relationship]['label_' . $this->relationType];
 529           }
 530           $statistics['filters'][$id]['value'] = $op .
 531             implode(', ', $relationshipTypes);
 532         }
 533
 534         //for displaying relationship status
 535         if ($value['title'] == 'Relationship Status') {
 536           $isStatusFilter = TRUE;
 537           $statistics['filters'][$id]['value'] = $relStatus;
 538         }
 539       }
 540     }
 541     //for displaying relationship status
 542     if (!$isStatusFilter && $relStatus) {
 543       $statistics['filters'][] = array(
 544         'title' => 'Relationship Status',
 545         'value' => $relStatus,
 546       );
 547     }
 548     return $statistics;
 549   }
 550
 551   public function groupBy() {
 552     $this->_groupBy = " ";
 553     $groupBy = array();
 554     if ($this->relationType == 'a_b') {
 555       $groupBy[] = " {$this->_aliases['civicrm_contact']}.id";
 556     }
 557     elseif ($this->relationType == 'b_a') {
 558       $groupBy[] = " {$this->_aliases['civicrm_contact_b']}.id";
 559     }
 560
 561     if (!empty($groupBy)) {
 562       $this->_groupBy = " GROUP BY  " . implode(', ', $groupBy) .
 563         " ,  {$this->_aliases['civicrm_relationship']}.id ";
 564     }
 565     else {
 566       $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_relationship']}.id ";
 567     }
 568   }
 569
 570   public function orderBy() {
 571     $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name, {$this->_aliases['civicrm_contact_b']}.sort_name ";
 572   }
 573
 574   public function postProcess() {
 575     $this->beginPostProcess();
 576
 577     $originalRelationshipTypeIdValue = $this->_params['relationship_type_id_value'];
 578     if (!empty($this->_params['relationship_type_id_value'])) {
 579       $relationshipTypes = array();
 580       $direction = array();
 581       $relType = array();
 582       foreach ($this->_params['relationship_type_id_value'] as $relationship_type) {
 583         $relType = explode('_', $relationship_type);
 584         $direction[] = $relType[1] . '_' . $relType[2];
 585         $relationshipTypes[] = intval($relType[0]);
 586       }
 587       // Lets take the first relationship type to guide us in the relationship direction
 588       // we should use.
 589       $this->relationType = $direction[0];
 590       $this->_params['relationship_type_id_value'] = $relationshipTypes;
 591     }
 592
 593     $this->buildACLClause(array(
 594       $this->_aliases['civicrm_contact'],
 595       $this->_aliases['civicrm_contact_b'],
 596     ));
 597     $sql = $this->buildQuery();
 598     $this->buildRows($sql, $rows);
 599
 600     $this->formatDisplay($rows);
 601     $this->doTemplateAssignment($rows);
 602
 603     if (!empty($originalRelationshipTypeIdValue)) {
 604       // store its old value, CRM-5837
 605       $this->_params['relationship_type_id_value'] = $originalRelationshipTypeIdValue;
 606     }
 607     $this->endPostProcess($rows);
 608   }
 609
 610   /**
 611    * @param $rows
 612    */
 613   public function alterDisplay(&$rows) {
 614     // custom code to alter rows
 615     $entryFound = FALSE;
 616
 617     foreach ($rows as $rowNum => $row) {
 618
 619       // handle country
 620       if (array_key_exists('civicrm_address_country_id', $row)) {
 621         if ($value = $row['civicrm_address_country_id']) {
 622           $rows[$rowNum]['civicrm_address_country_id'] = CRM_Core_PseudoConstant::country($value, FALSE);
 623         }
 624         $entryFound = TRUE;
 625       }
 626
 627       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 628         if ($value = $row['civicrm_address_state_province_id']) {
 629           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 630         }
 631         $entryFound = TRUE;
 632       }
 633
 634       if (array_key_exists('civicrm_contact_sort_name_a', $row) &&
 635         array_key_exists('civicrm_contact_id', $row)
 636       ) {
 637         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 638           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 639           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 640         );
 641         $rows[$rowNum]['civicrm_contact_sort_name_a']
 642           = $rows[$rowNum]['civicrm_contact_sort_name_a'] . ' (' .
 643           $rows[$rowNum]['civicrm_contact_id'] . ')';
 644         $rows[$rowNum]['civicrm_contact_sort_name_a_link'] = $url;
 645         $rows[$rowNum]['civicrm_contact_sort_name_a_hover'] = ts("View Contact details for this contact.");
 646         $entryFound = TRUE;
 647       }
 648
 649       if (array_key_exists('civicrm_contact_b_sort_name_b', $row) &&
 650         array_key_exists('civicrm_contact_b_id', $row)
 651       ) {
 652         $url = CRM_Report_Utils_Report::getNextUrl('contact/detail',
 653           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_b_id'],
 654           $this->_absoluteUrl, $this->_id, $this->_drilldownReport
 655         );
 656         $rows[$rowNum]['civicrm_contact_b_sort_name_b']
 657           = $rows[$rowNum]['civicrm_contact_b_sort_name_b'] . ' (' .
 658           $rows[$rowNum]['civicrm_contact_b_id'] . ')';
 659         $rows[$rowNum]['civicrm_contact_b_sort_name_b_link'] = $url;
 660         $rows[$rowNum]['civicrm_contact_b_sort_name_b_hover'] = ts("View Contact details for this contact.");
 661         $entryFound = TRUE;
 662       }
 663
 664       if (array_key_exists('civicrm_relationship_relationship_id', $row) &&
 665         array_key_exists('civicrm_contact_id', $row)
 666       ) {
 667         $url = "/civicrm/contact/view/rel?reset=1&action=update&rtype=a_b&cid=" .
 668           $row['civicrm_contact_id'] . "&id=" .
 669           $row['civicrm_relationship_relationship_id'];
 670         $rows[$rowNum]['civicrm_relationship_relationship_id_link'] = $url;
 671         $rows[$rowNum]['civicrm_relationship_relationship_id_hover'] = ts("Edit this relationship.");
 672         $entryFound = TRUE;
 673       }
 674
 675       // skip looking further in rows, if first row itself doesn't
 676       // have the column we need
 677       if (!$entryFound) {
 678         break;
 679       }
 680     }
 681   }
 682
 683   /**
 684    * @param $valid bool - set to 1 if we are looking for a valid relationship, 0 if not
 685    *
 686    * @return array
 687    */
 688   public function buildValidityQuery($valid) {
 689     $clause = NULL;
 690     if ($valid == '1') {
 691       // relationships dates are not expired
 692       $clause = "((start_date <= CURDATE() OR start_date is null) AND (end_date >= CURDATE() OR end_date is null))";
 693     }
 694     elseif ($valid == '0') {
 695       // relationships dates are expired or has not started yet
 696       $clause = "(start_date >= CURDATE() OR end_date < CURDATE())";
 697     }
 698     return $clause;
 699   }
 700
 701 }