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