CRM/Report/Form/Contact/Relationship.php

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