CRM/Report/Form/Contact/Relationship.php

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