CRM/Report/Form/Contact/Detail.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   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-2017
  32  */
  33 class CRM_Report_Form_Contact_Detail extends CRM_Report_Form {
  34   const ROW_COUNT_LIMIT = 10;
  35
  36   protected $_summary = NULL;
  37
  38   protected $_customGroupExtends = array(
  39     'Contact',
  40     'Individual',
  41     'Household',
  42     'Organization',
  43   );
  44
  45   /**
  46    * This report has not been optimised for group filtering.
  47    *
  48    * The functionality for group filtering has been improved but not
  49    * all reports have been adjusted to take care of it. This report has not
  50    * and will run an inefficient query until fixed.
  51    *
  52    * CRM-19170
  53    *
  54    * @var bool
  55    */
  56   protected $groupFilterNotOptimised = TRUE;
  57
  58   /**
  59    * Class constructor.
  60    */
  61   public function __construct() {
  62     $this->_autoIncludeIndexedFieldsAsOrderBys = 1;
  63     $this->_columns = array(
  64       'civicrm_contact' => array(
  65         'dao' => 'CRM_Contact_DAO_Contact',
  66         'fields' => array(
  67           'sort_name' => array(
  68             'title' => ts('Contact Name'),
  69             'required' => TRUE,
  70             'no_repeat' => TRUE,
  71           ),
  72           'first_name' => array(
  73             'title' => ts('First Name'),
  74           ),
  75           'middle_name' => array(
  76             'title' => ts('Middle Name'),
  77           ),
  78           'last_name' => array(
  79             'title' => ts('Last Name'),
  80           ),
  81           'id' => array(
  82             'no_display' => TRUE,
  83             'required' => TRUE,
  84           ),
  85           'gender_id' => array(
  86             'title' => ts('Gender'),
  87           ),
  88           'birth_date' => array(
  89             'title' => ts('Birth Date'),
  90           ),
  91           'age' => array(
  92             'title' => ts('Age'),
  93             'dbAlias' => 'TIMESTAMPDIFF(YEAR, contact_civireport.birth_date, CURDATE())',
  94           ),
  95           'contact_type' => array(
  96             'title' => ts('Contact Type'),
  97           ),
  98           'contact_sub_type' => array(
  99             'title' => ts('Contact Subtype'),
 100           ),
 101         ),
 102         'filters' => $this->getBasicContactFilters(),
 103         'grouping' => 'contact-fields',
 104         'order_bys' => array(
 105           'sort_name' => array(
 106             'title' => ts('Last Name, First Name'),
 107             'default' => '1',
 108             'default_weight' => '0',
 109             'default_order' => 'ASC',
 110           ),
 111           'first_name' => array(
 112             'title' => ts('First Name'),
 113           ),
 114           'gender_id' => array(
 115             'name' => 'gender_id',
 116             'title' => ts('Gender'),
 117           ),
 118           'birth_date' => array(
 119             'name' => 'birth_date',
 120             'title' => ts('Birth Date'),
 121           ),
 122           'contact_type' => array(
 123             'title' => ts('Contact Type'),
 124           ),
 125           'contact_sub_type' => array(
 126             'title' => ts('Contact Subtype'),
 127           ),
 128         ),
 129       ),
 130       'civicrm_address' => array(
 131         'dao' => 'CRM_Core_DAO_Address',
 132         'grouping' => 'contact-fields',
 133         'fields' => array(
 134           'street_address' => NULL,
 135           'city' => NULL,
 136           'postal_code' => NULL,
 137           'state_province_id' => array(
 138             'title' => ts('State/Province'),
 139           ),
 140         ),
 141         'order_bys' => array(
 142           'state_province_id' => array('title' => ts('State/Province')),
 143           'city' => array('title' => ts('City')),
 144           'postal_code' => array('title' => ts('Postal Code')),
 145         ),
 146       ),
 147       'civicrm_country' => array(
 148         'dao' => 'CRM_Core_DAO_Country',
 149         'fields' => array(
 150           'name' => array('title' => ts('Country'), 'default' => TRUE),
 151         ),
 152         'order_bys' => array(
 153           'name' => array('title' => ts('Country')),
 154         ),
 155         'grouping' => 'contact-fields',
 156       ),
 157       'civicrm_email' => array(
 158         'dao' => 'CRM_Core_DAO_Email',
 159         'fields' => array(
 160           'email' => array(
 161             'title' => ts('Email'),
 162             'no_repeat' => TRUE,
 163           ),
 164         ),
 165         'grouping' => 'contact-fields',
 166         'order_bys' => array(
 167           'email' => array(
 168             'title' => ts('Email'),
 169           ),
 170         ),
 171       ),
 172       'civicrm_contribution' => array(
 173         'dao' => 'CRM_Contribute_DAO_Contribution',
 174         'fields' => array(
 175           'contact_id' => array(
 176             'no_display' => TRUE,
 177             'required' => TRUE,
 178           ),
 179           'contribution_id' => array(
 180             'title' => ts('Contribution'),
 181             'no_repeat' => TRUE,
 182             'default' => TRUE,
 183           ),
 184           'total_amount' => array('default' => TRUE),
 185           'financial_type_id' => array(
 186             'title' => ts('Financial Type'),
 187             'default' => TRUE,
 188           ),
 189           'trxn_id' => NULL,
 190           'receive_date' => array('default' => TRUE),
 191           'receipt_date' => NULL,
 192           'contribution_status_id' => array(
 193             'title' => ts('Contribution Status'),
 194             'default' => TRUE,
 195           ),
 196           'contribution_source' => NULL,
 197         ),
 198       ),
 199       'civicrm_membership' => array(
 200         'dao' => 'CRM_Member_DAO_Membership',
 201         'fields' => array(
 202           'contact_id' => array(
 203             'no_display' => TRUE,
 204             'required' => TRUE,
 205           ),
 206           'membership_id' => array(
 207             'title' => ts('Membership'),
 208             'no_repeat' => TRUE,
 209             'default' => TRUE,
 210           ),
 211           'membership_type_id' => array(
 212             'title' => ts('Membership Type'),
 213             'default' => TRUE,
 214           ),
 215           'join_date' => NULL,
 216           'membership_start_date' => array(
 217             'title' => ts('Start Date'),
 218             'default' => TRUE,
 219           ),
 220           'membership_end_date' => array(
 221             'title' => ts('End Date'),
 222             'default' => TRUE,
 223           ),
 224           'membership_status_id' => array(
 225             'name' => 'status_id',
 226             'title' => ts('Membership Status'),
 227             'default' => TRUE,
 228           ),
 229           'source' => array('title' => ts('Membership Source')),
 230         ),
 231       ),
 232       'civicrm_participant' => array(
 233         'dao' => 'CRM_Event_DAO_Participant',
 234         'fields' => array(
 235           'contact_id' => array(
 236             'no_display' => TRUE,
 237             'required' => TRUE,
 238           ),
 239           'participant_id' => array(
 240             'title' => ts('Participant'),
 241             'no_repeat' => TRUE,
 242             'default' => TRUE,
 243           ),
 244           'event_id' => array('default' => TRUE),
 245           'participant_status_id' => array(
 246             'name' => 'status_id',
 247             'title' => ts('Participant Status'),
 248             'default' => TRUE,
 249           ),
 250           'role_id' => array(
 251             'title' => ts('Role'),
 252             'default' => TRUE,
 253           ),
 254           'participant_register_date' => array(
 255             'title' => ts('Register Date'),
 256             'default' => TRUE,
 257           ),
 258           'fee_level' => array(
 259             'title' => ts('Fee Level'),
 260             'default' => TRUE,
 261           ),
 262           'fee_amount' => array(
 263             'title' => ts('Fee Amount'),
 264             'default' => TRUE,
 265           ),
 266         ),
 267       ),
 268       'civicrm_relationship' => array(
 269         'dao' => 'CRM_Contact_DAO_Relationship',
 270         'fields' => array(
 271           'relationship_id' => array(
 272             'name' => 'id',
 273             'title' => ts('Relationship'),
 274             'no_repeat' => TRUE,
 275             'default' => TRUE,
 276           ),
 277           'relationship_type_id' => array(
 278             'title' => ts('Relationship Type'),
 279             'default' => TRUE,
 280           ),
 281           'contact_id_b' => array(
 282             'title' => ts('Relationship With'),
 283             'default' => TRUE,
 284           ),
 285           'start_date' => array(
 286             'title' => ts('Start Date'),
 287             'type' => CRM_Report_Form::OP_DATE,
 288           ),
 289           'end_date' => array(
 290             'title' => ts('End Date'),
 291             'type' => CRM_Report_Form::OP_DATE,
 292           ),
 293         ),
 294       ),
 295       'civicrm_activity' => array(
 296         'dao' => 'CRM_Activity_DAO_Activity',
 297         'fields' => array(
 298           'id' => array(
 299             'title' => ts('Activity'),
 300             'no_repeat' => TRUE,
 301             'default' => TRUE,
 302           ),
 303           'activity_type_id' => array(
 304             'title' => ts('Activity Type'),
 305             'default' => TRUE,
 306           ),
 307           'subject' => array(
 308             'title' => ts('Subject'),
 309             'default' => TRUE,
 310           ),
 311           'activity_date_time' => array(
 312             'title' => ts('Activity Date'),
 313             'default' => TRUE,
 314           ),
 315           'activity_status_id' => array(
 316             'name' => 'status_id',
 317             'title' => ts('Activity Status'),
 318             'default' => TRUE,
 319           ),
 320         ),
 321         'grouping' => 'activity-fields',
 322       ),
 323       'civicrm_activity_target' => array(
 324         'dao' => 'CRM_Activity_DAO_ActivityContact',
 325         'fields' => array(
 326           'target_contact_id' => array(
 327             'title' => ts('With Contact'),
 328             'name' => 'contact_id',
 329             'default' => TRUE,
 330           ),
 331         ),
 332         'grouping' => 'activity-fields',
 333       ),
 334       'civicrm_activity_assignment' => array(
 335         'dao' => 'CRM_Activity_DAO_ActivityContact',
 336         'fields' => array(
 337           'assignee_contact_id' => array(
 338             'title' => ts('Assigned to'),
 339             'name' => 'contact_id',
 340             'default' => TRUE,
 341           ),
 342         ),
 343         'grouping' => 'activity-fields',
 344       ),
 345       'civicrm_activity_source' => array(
 346         'dao' => 'CRM_Activity_DAO_ActivityContact',
 347         'fields' => array(
 348           'source_contact_id' => array(
 349             'title' => ts('Added By'),
 350             'name' => 'contact_id',
 351             'default' => TRUE,
 352           ),
 353         ),
 354         'grouping' => 'activity-fields',
 355       ),
 356       'civicrm_phone' => array(
 357         'dao' => 'CRM_Core_DAO_Phone',
 358         'fields' => array(
 359           'phone' => NULL,
 360           'phone_ext' => array(
 361             'title' => ts('Phone Extension'),
 362           ),
 363         ),
 364         'grouping' => 'contact-fields',
 365       ),
 366     );
 367     $this->_groupFilter = TRUE;
 368     $this->_tagFilter = TRUE;
 369     parent::__construct();
 370   }
 371
 372   public function preProcess() {
 373     $this->_csvSupported = FALSE;
 374     parent::preProcess();
 375   }
 376
 377   public function select() {
 378     $select = array();
 379     $this->_columnHeaders = array();
 380     $this->_component = array(
 381       'contribution_civireport',
 382       'membership_civireport',
 383       'participant_civireport',
 384       'relationship_civireport',
 385       'activity_civireport',
 386     );
 387     foreach ($this->_columns as $tableName => $table) {
 388       if (array_key_exists('fields', $table)) {
 389         foreach ($table['fields'] as $fieldName => $field) {
 390           if (!empty($field['required']) ||
 391             !empty($this->_params['fields'][$fieldName])
 392           ) {
 393             //isolate the select clause compoenent wise
 394             if (in_array($table['alias'], $this->_component)) {
 395               $select[$table['alias']][] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 396               $this->_columnHeadersComponent[$table['alias']]["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 397               $this->_columnHeadersComponent[$table['alias']]["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 398             }
 399             elseif ($table['alias'] ==
 400               $this->_aliases['civicrm_activity_target'] ||
 401               $table['alias'] ==
 402               $this->_aliases['civicrm_activity_assignment'] ||
 403               $table['alias'] == $this->_aliases['civicrm_activity_source']
 404             ) {
 405               if ($table['alias'] == $this->_aliases['civicrm_activity_target']
 406               ) {
 407                 $addContactId = 'civicrm_activity_target.contact_id as target_contact_id';
 408               }
 409               elseif ($table['alias'] ==
 410                 $this->_aliases['civicrm_activity_source']
 411               ) {
 412                 $addContactId = 'civicrm_activity_source.contact_id';
 413               }
 414               else {
 415                 $addContactId = 'civicrm_activity_assignment.contact_id as assignee_contact_id';
 416               }
 417
 418               $tableName = $table['alias'];
 419               $select['activity_civireport'][] = "$tableName.display_name as {$tableName}_{$fieldName}, $addContactId ";
 420               $this->_columnHeadersComponent['activity_civireport']["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 421               $this->_columnHeadersComponent['activity_civireport']["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value('title', $field);
 422             }
 423             else {
 424               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 425               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 426               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 427             }
 428           }
 429         }
 430       }
 431     }
 432
 433     foreach ($this->_component as $val) {
 434       if (!empty($select[$val])) {
 435         $this->_selectComponent[$val] = "SELECT " . implode(', ', $select[$val]) . " ";
 436         unset($select[$val]);
 437       }
 438     }
 439
 440     $this->_select = "SELECT " . implode(', ', $select) . " ";
 441   }
 442
 443   /**
 444    * @param $fields
 445    * @param $files
 446    * @param $self
 447    *
 448    * @return array
 449    */
 450   public static function formRule($fields, $files, $self) {
 451     $errors = array();
 452     return $errors;
 453   }
 454
 455   public function from() {
 456     $group = " ";
 457     $this->_from = "
 458         FROM civicrm_contact {$this->_aliases['civicrm_contact']} {$this->_aclFrom}";
 459
 460     if ($this->isTableSelected('civicrm_country') ||
 461       $this->isTableSelected('civicrm_address')
 462     ) {
 463       $this->_from .= "
 464             LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 465                    ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND
 466                       {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
 467     }
 468
 469     if ($this->isTableSelected('civicrm_email')) {
 470       $this->_from .= "
 471             LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']}
 472                    ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 473                       {$this->_aliases['civicrm_email']}.is_primary = 1) ";
 474     }
 475
 476     if ($this->isTableSelected('civicrm_phone')) {
 477       $this->_from .= "
 478             LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 479                    ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 480                       {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
 481     }
 482
 483     if ($this->isTableSelected('civicrm_country')) {
 484       $this->_from .= "
 485             LEFT JOIN civicrm_country {$this->_aliases['civicrm_country']}
 486                    ON {$this->_aliases['civicrm_address']}.country_id = {$this->_aliases['civicrm_country']}.id AND
 487                       {$this->_aliases['civicrm_address']}.is_primary = 1 ";
 488     }
 489
 490     $this->_from .= "{$group}";
 491
 492     foreach ($this->_component as $val) {
 493       if (!empty($this->_selectComponent['contribution_civireport'])) {
 494         $this->_formComponent['contribution_civireport'] = " FROM
 495                             civicrm_contact  {$this->_aliases['civicrm_contact']}
 496                             INNER JOIN civicrm_contribution       {$this->_aliases['civicrm_contribution']}
 497                                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id
 498                             {$group}
 499                     ";
 500       }
 501       if (!empty($this->_selectComponent['membership_civireport'])) {
 502         $this->_formComponent['membership_civireport'] = " FROM
 503                             civicrm_contact  {$this->_aliases['civicrm_contact']}
 504                             INNER JOIN civicrm_membership       {$this->_aliases['civicrm_membership']}
 505                                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_membership']}.contact_id
 506                             {$group} ";
 507       }
 508       if (!empty($this->_selectComponent['participant_civireport'])) {
 509         $this->_formComponent['participant_civireport'] = " FROM
 510                             civicrm_contact  {$this->_aliases['civicrm_contact']}
 511                             INNER JOIN civicrm_participant       {$this->_aliases['civicrm_participant']}
 512                                     ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_participant']}.contact_id
 513                             {$group} ";
 514       }
 515
 516       if (!empty($this->_selectComponent['activity_civireport'])) {
 517         $activityContacts = CRM_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
 518         $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
 519         $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
 520         $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
 521
 522         $this->_formComponent['activity_civireport'] = "FROM
 523                         civicrm_activity {$this->_aliases['civicrm_activity']}
 524                         LEFT JOIN civicrm_activity_contact civicrm_activity_target ON
 525                             {$this->_aliases['civicrm_activity']}.id = civicrm_activity_target.activity_id AND
 526                             civicrm_activity_target.record_type_id = {$targetID}
 527                         LEFT JOIN civicrm_activity_contact civicrm_activity_assignment ON
 528                             {$this->_aliases['civicrm_activity']}.id = civicrm_activity_assignment.activity_id AND                                                                      civicrm_activity_assignment.record_type_id = {$assigneeID}
 529                         LEFT JOIN civicrm_activity_contact civicrm_activity_source
 530                             ON {$this->_aliases['civicrm_activity']}.id = civicrm_activity_source.activity_id AND
 531                             civicrm_activity_source.record_type_id = {$sourceID}
 532                         LEFT JOIN civicrm_contact {$this->_aliases['civicrm_activity_target']} ON
 533                             civicrm_activity_target.contact_id = {$this->_aliases['civicrm_activity_target']}.id
 534
 535                         LEFT JOIN civicrm_contact {$this->_aliases['civicrm_activity_assignment']} ON
 536                             civicrm_activity_assignment.contact_id = {$this->_aliases['civicrm_activity_assignment']}.id
 537                         LEFT JOIN civicrm_contact {$this->_aliases['civicrm_activity_source']} ON
 538                             civicrm_activity_source.contact_id = {$this->_aliases['civicrm_activity_source']}.id
 539                         LEFT JOIN civicrm_option_value ON
 540                             ( {$this->_aliases['civicrm_activity']}.activity_type_id = civicrm_option_value.value )
 541                         LEFT JOIN civicrm_option_group ON
 542                             civicrm_option_group.id = civicrm_option_value.option_group_id
 543                         LEFT JOIN civicrm_case_activity ON
 544                             civicrm_case_activity.activity_id = {$this->_aliases['civicrm_activity']}.id
 545                         LEFT JOIN civicrm_case ON
 546                             civicrm_case_activity.case_id = civicrm_case.id
 547                         LEFT JOIN civicrm_case_contact ON
 548                             civicrm_case_contact.case_id = civicrm_case.id ";
 549       }
 550
 551       if (!empty($this->_selectComponent['relationship_civireport'])) {
 552         $this->_formComponent['relationship_civireport'] = "FROM
 553                             civicrm_relationship {$this->_aliases['civicrm_relationship']}
 554
 555                             LEFT JOIN civicrm_contact  {$this->_aliases['civicrm_contact']} ON
 556                                 {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_relationship']}.contact_id_b
 557                             LEFT JOIN civicrm_contact  contact_a ON
 558                                contact_a.id = {$this->_aliases['civicrm_relationship']}.contact_id_a ";
 559       }
 560     }
 561   }
 562
 563   public function where() {
 564     $clauses = array();
 565
 566     foreach ($this->_columns as $tableName => $table) {
 567       if (array_key_exists('filters', $table)) {
 568         foreach ($table['filters'] as $fieldName => $field) {
 569           $clause = NULL;
 570           if (CRM_Utils_Array::value('operatorType', $field) & CRM_Report_Form::OP_DATE
 571           ) {
 572             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 573             $from = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 574             $to = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 575
 576             $clause = $this->dateClause($field['dbAlias'], $relative, $from, $to);
 577           }
 578           else {
 579             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 580             $clause = $this->whereClause($field,
 581               $op,
 582               CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 583               CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 584               CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 585             );
 586           }
 587           if (!empty($clause)) {
 588             $clauses[] = $clause;
 589           }
 590         }
 591       }
 592     }
 593
 594     if (empty($clauses)) {
 595       $this->_where = "WHERE ( 1 ) ";
 596     }
 597     else {
 598       $this->_where = "WHERE " . implode(' AND ', $clauses);
 599     }
 600
 601     if ($this->_aclWhere) {
 602       $this->_where .= " AND {$this->_aclWhere} ";
 603     }
 604   }
 605
 606   /**
 607    * @return array
 608    */
 609   public function clauseComponent() {
 610     $selectedContacts = implode(',', $this->_contactSelected);
 611     $contribution = $membership = $participant = NULL;
 612     $eligibleResult = $rows = $tempArray = array();
 613     foreach ($this->_component as $val) {
 614       if (!empty($this->_selectComponent[$val]) &&
 615         ($val != 'activity_civireport' && $val != 'relationship_civireport')
 616       ) {
 617         $sql = "{$this->_selectComponent[$val]} {$this->_formComponent[$val]}
 618                          WHERE    {$this->_aliases['civicrm_contact']}.id IN ( $selectedContacts )
 619                           ";
 620
 621         $dao = CRM_Core_DAO::executeQuery($sql);
 622         while ($dao->fetch()) {
 623           $countRecord = 0;
 624           $eligibleResult[$val] = $val;
 625           $CC = 'civicrm_' . substr_replace($val, '', -11, 11) . '_contact_id';
 626           $row = array();
 627           foreach ($this->_columnHeadersComponent[$val] as $key => $value) {
 628             $countRecord++;
 629             $row[$key] = $dao->$key;
 630           }
 631
 632           //if record exist for component(except contact_id)
 633           //since contact_id is selected for every component
 634           if ($countRecord > 1) {
 635             $rows[$dao->$CC][$val][] = $row;
 636           }
 637           $tempArray[$dao->$CC] = $dao->$CC;
 638         }
 639       }
 640     }
 641
 642     if (!empty($this->_selectComponent['relationship_civireport'])) {
 643
 644       $relTypes = CRM_Contact_BAO_Relationship::getContactRelationshipType(NULL, NULL, NULL, NULL, TRUE);
 645
 646       $val = 'relationship_civireport';
 647       $eligibleResult[$val] = $val;
 648       $sql = "{$this->_selectComponent[$val]},{$this->_aliases['civicrm_contact']}.display_name as contact_b_name,  contact_a.id as contact_a_id , contact_a.display_name  as contact_a_name  {$this->_formComponent[$val]}
 649                          WHERE    ({$this->_aliases['civicrm_contact']}.id IN ( $selectedContacts )
 650                                   OR
 651                                   contact_a.id IN ( $selectedContacts ) ) AND
 652                                   {$this->_aliases['civicrm_relationship']}.is_active = 1 AND
 653                                   contact_a.is_deleted = 0 AND
 654                                   {$this->_aliases['civicrm_contact']}.is_deleted = 0
 655                          ";
 656
 657       $dao = CRM_Core_DAO::executeQuery($sql);
 658       while ($dao->fetch()) {
 659         foreach ($this->_columnHeadersComponent[$val] as $key => $value) {
 660           if ($key == 'civicrm_relationship_contact_id_b') {
 661             $row[$key] = $dao->contact_b_name;
 662             continue;
 663           }
 664
 665           $row[$key] = $dao->$key;
 666         }
 667
 668         $relTitle = "" . $dao->civicrm_relationship_relationship_type_id .
 669           '_a_b';
 670         $row['civicrm_relationship_relationship_type_id'] = $relTypes[$relTitle];
 671
 672         $rows[$dao->contact_a_id][$val][] = $row;
 673
 674         $row['civicrm_relationship_contact_id_b'] = $dao->contact_a_name;
 675         $relTitle = "" . $dao->civicrm_relationship_relationship_type_id .
 676           '_b_a';
 677         if (isset($relTypes[$relTitle])) {
 678           $row['civicrm_relationship_relationship_type_id'] = $relTypes[$relTitle];
 679         }
 680         $rows[$dao->civicrm_relationship_contact_id_b][$val][] = $row;
 681       }
 682     }
 683
 684     if (!empty($this->_selectComponent['activity_civireport'])) {
 685
 686       $componentClause = "civicrm_option_value.component_id IS NULL";
 687       $componentsIn = NULL;
 688       $compInfo = CRM_Core_Component::getEnabledComponents();
 689       foreach ($compInfo as $compObj) {
 690         if ($compObj->info['showActivitiesInCore']) {
 691           $componentsIn = $componentsIn ? ($componentsIn . ', ' .
 692             $compObj->componentID) : $compObj->componentID;
 693         }
 694       }
 695       if ($componentsIn) {
 696         $componentClause = "( $componentClause OR
 697                                       civicrm_option_value.component_id IN ($componentsIn) )";
 698       }
 699
 700       $val = 'activity_civireport';
 701       $eligibleResult[$val] = $val;
 702       $sql = "{$this->_selectComponent[$val]} ,
 703                  {$this->_aliases['civicrm_activity_source']}.display_name as added_by {$this->_formComponent[$val]}
 704
 705                  WHERE ( civicrm_activity_source.contact_id IN ($selectedContacts) OR
 706                          civicrm_activity_target.contact_id IN ($selectedContacts) OR
 707                          civicrm_activity_assignment.contact_id IN ($selectedContacts) OR
 708                          civicrm_case_contact.contact_id IN ($selectedContacts) ) AND
 709                          civicrm_option_group.name = 'activity_type' AND
 710                          {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 711                          ($componentClause)
 712                  ORDER BY {$this->_aliases['civicrm_activity']}.activity_date_time desc  ";
 713
 714       $dao = CRM_Core_DAO::executeQuery($sql);
 715       while ($dao->fetch()) {
 716         foreach ($this->_columnHeadersComponent[$val] as $key => $value) {
 717           if ($key == 'civicrm_activity_source_contact_id') {
 718             $row[$key] = $dao->added_by;
 719             continue;
 720           }
 721           $row[$key] = $dao->$key;
 722         }
 723
 724         if (isset($dao->civicrm_activity_source_contact_id)) {
 725           $rows[$dao->civicrm_activity_source_contact_id][$val][] = $row;
 726         }
 727         if (isset($dao->target_contact_id)) {
 728           $rows[$dao->target_contact_id][$val][] = $row;
 729         }
 730         if (isset($dao->assignee_contact_id)) {
 731           $rows[$dao->assignee_contact_id][$val][] = $row;
 732         }
 733       }
 734
 735       //unset the component header if data is not present
 736       foreach ($this->_component as $val) {
 737         if (!in_array($val, $eligibleResult)) {
 738
 739           unset($this->_columnHeadersComponent[$val]);
 740         }
 741       }
 742     }
 743
 744     return $rows;
 745   }
 746
 747   /**
 748    * @param $rows
 749    *
 750    * @return array
 751    */
 752   public function statistics(&$rows) {
 753     $statistics = array();
 754
 755     $count = count($rows);
 756     if ($this->_rollup && ($this->_rollup != '')) {
 757       $count++;
 758     }
 759
 760     $this->countStat($statistics, $count);
 761     $this->filterStat($statistics);
 762
 763     return $statistics;
 764   }
 765
 766   /**
 767    * Override to set limit is 10
 768    * @param int $rowCount
 769    */
 770   public function limit($rowCount = self::ROW_COUNT_LIMIT) {
 771     parent::limit($rowCount);
 772   }
 773
 774   /**
 775    * Override to set pager with limit is 10
 776    * @param int $rowCount
 777    */
 778   public function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 779     parent::setPager($rowCount);
 780   }
 781
 782   public function postProcess() {
 783
 784     $this->beginPostProcess();
 785
 786     // get the acl clauses built before we assemble the query
 787     $this->buildACLClause($this->_aliases['civicrm_contact']);
 788
 789     $sql = $this->buildQuery(TRUE);
 790
 791     $rows = $graphRows = $this->_contactSelected = array();
 792     $this->buildRows($sql, $rows);
 793     foreach ($rows as $key => $val) {
 794       $rows[$key]['contactID'] = $val['civicrm_contact_id'];
 795       $this->_contactSelected[] = $val['civicrm_contact_id'];
 796     }
 797
 798     $this->formatDisplay($rows);
 799
 800     if (!empty($this->_contactSelected)) {
 801       $componentRows = $this->clauseComponent();
 802       $this->alterComponentDisplay($componentRows);
 803
 804       //unset Conmponent id and contact id from display
 805       foreach ($this->_columnHeadersComponent as $componentTitle => $headers) {
 806         $id_header = 'civicrm_' . substr_replace($componentTitle, '', -11, 11) . '_' .
 807           substr_replace($componentTitle, '', -11, 11) . '_id';
 808         $contact_header = 'civicrm_' . substr_replace($componentTitle, '', -11, 11) .
 809           '_contact_id';
 810         if ($componentTitle == 'activity_civireport') {
 811           $id_header = 'civicrm_' . substr_replace($componentTitle, '', -11, 11) . '_id';
 812         }
 813
 814         unset($this->_columnHeadersComponent[$componentTitle][$id_header]);
 815         unset($this->_columnHeadersComponent[$componentTitle][$contact_header]);
 816       }
 817
 818       $this->assign_by_ref('columnHeadersComponent', $this->_columnHeadersComponent);
 819       $this->assign_by_ref('componentRows', $componentRows);
 820     }
 821
 822     $this->doTemplateAssignment($rows);
 823     $this->endPostProcess();
 824   }
 825
 826   /**
 827    * Alter display of rows.
 828    *
 829    * Iterate through the rows retrieved via SQL and make changes for display purposes,
 830    * such as rendering contacts as links.
 831    *
 832    * @param array $rows
 833    *   Rows generated by SQL, with an array for each row.
 834    */
 835   public function alterDisplay(&$rows) {
 836
 837     $entryFound = FALSE;
 838
 839     foreach ($rows as $rowNum => $row) {
 840       // make count columns point to detail report
 841
 842       // change contact name with link
 843       if (array_key_exists('civicrm_contact_sort_name', $row) &&
 844         array_key_exists('civicrm_contact_id', $row)
 845       ) {
 846
 847         $url = CRM_Utils_System::url('civicrm/contact/view',
 848           'reset=1&cid=' . $row['civicrm_contact_id'],
 849           $this->_absoluteUrl
 850         );
 851         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 852         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts('View Contact Record');
 853         $entryFound = TRUE;
 854       }
 855
 856       // Handle ID to label conversion for contact fields
 857       $entryFound = $this->alterDisplayContactFields($row, $rows, $rowNum, NULL, NULL) ? TRUE : $entryFound;
 858
 859       // display birthday in the configured custom format
 860       if (array_key_exists('civicrm_contact_birth_date', $row)) {
 861         $birthDate = $row['civicrm_contact_birth_date'];
 862         if ($birthDate) {
 863           $rows[$rowNum]['civicrm_contact_birth_date'] = CRM_Utils_Date::customFormat($birthDate, '%Y%m%d');
 864         }
 865         $entryFound = TRUE;
 866       }
 867
 868       if (array_key_exists('civicrm_address_state_province_id', $row)) {
 869         if ($value = $row['civicrm_address_state_province_id']) {
 870           $rows[$rowNum]['civicrm_address_state_province_id'] = CRM_Core_PseudoConstant::stateProvince($value, FALSE);
 871         }
 872         $entryFound = TRUE;
 873       }
 874
 875       // skip looking further in rows, if first row itself doesn't
 876       // have the column we need
 877       if (!$entryFound) {
 878         break;
 879       }
 880     }
 881   }
 882
 883   /**
 884    * @param $componentRows
 885    */
 886   public function alterComponentDisplay(&$componentRows) {
 887     // custom code to alter rows
 888     $activityTypes = CRM_Core_PseudoConstant::activityType(TRUE, TRUE, FALSE, 'label', TRUE);
 889     $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 890
 891     $entryFound = FALSE;
 892     foreach ($componentRows as $contactID => $components) {
 893       foreach ($components as $component => $rows) {
 894         foreach ($rows as $rowNum => $row) {
 895           // handle contribution
 896           if ($component == 'contribution_civireport') {
 897             if ($val = CRM_Utils_Array::value('civicrm_contribution_financial_type_id', $row)) {
 898               $componentRows[$contactID][$component][$rowNum]['civicrm_contribution_financial_type_id'] = CRM_Contribute_PseudoConstant::financialType($val, FALSE);
 899             }
 900
 901             if ($val = CRM_Utils_Array::value('civicrm_contribution_contribution_status_id', $row)) {
 902               $componentRows[$contactID][$component][$rowNum]['civicrm_contribution_contribution_status_id'] = CRM_Contribute_PseudoConstant::contributionStatus($val);
 903             }
 904             $entryFound = TRUE;
 905           }
 906
 907           if ($component == 'membership_civireport') {
 908             if ($val = CRM_Utils_Array::value('civicrm_membership_membership_type_id', $row)) {
 909               $componentRows[$contactID][$component][$rowNum]['civicrm_membership_membership_type_id'] = CRM_Member_PseudoConstant::membershipType($val, FALSE);
 910             }
 911
 912             if ($val = CRM_Utils_Array::value('civicrm_membership_status_id', $row)) {
 913               $componentRows[$contactID][$component][$rowNum]['civicrm_membership_status_id'] = CRM_Member_PseudoConstant::membershipStatus($val, FALSE);
 914             }
 915             $entryFound = TRUE;
 916           }
 917
 918           if ($component == 'participant_civireport') {
 919             if ($val = CRM_Utils_Array::value('civicrm_participant_event_id', $row)) {
 920               $componentRows[$contactID][$component][$rowNum]['civicrm_participant_event_id'] = CRM_Event_PseudoConstant::event($val, FALSE);
 921               $url = CRM_Report_Utils_Report::getNextUrl('event/income',
 922                 'reset=1&force=1&id_op=in&id_value=' . $val,
 923                 $this->_absoluteUrl, $this->_id
 924               );
 925               $componentRows[$contactID][$component][$rowNum]['civicrm_participant_event_id_link'] = $url;
 926               $componentRows[$contactID][$component][$rowNum]['civicrm_participant_event_id_hover'] = ts('View Event Income details for this Event.');
 927               $entryFound = TRUE;
 928             }
 929
 930             if ($val = CRM_Utils_Array::value('civicrm_participant_participant_status_id', $row)) {
 931               $componentRows[$contactID][$component][$rowNum]['civicrm_participant_participant_status_id'] = CRM_Event_PseudoConstant::participantStatus($val, FALSE);
 932             }
 933             if ($val = CRM_Utils_Array::value('civicrm_participant_role_id', $row)) {
 934               $roles = explode(CRM_Core_DAO::VALUE_SEPARATOR, $val);
 935               $value = array();
 936               foreach ($roles as $role) {
 937                 $value[$role] = CRM_Event_PseudoConstant::participantRole($role, FALSE);
 938               }
 939               $componentRows[$contactID][$component][$rowNum]['civicrm_participant_role_id'] = implode(', ', $value);
 940             }
 941
 942             $entryFound = TRUE;
 943           }
 944
 945           if ($component == 'activity_civireport') {
 946             if ($val = CRM_Utils_Array::value('civicrm_activity_activity_type_id', $row)) {
 947               $componentRows[$contactID][$component][$rowNum]['civicrm_activity_activity_type_id'] = $activityTypes[$val];
 948             }
 949             if ($val = CRM_Utils_Array::value('civicrm_activity_activity_status_id', $row)) {
 950               $componentRows[$contactID][$component][$rowNum]['civicrm_activity_activity_status_id'] = $activityStatus[$val];
 951             }
 952
 953             $entryFound = TRUE;
 954           }
 955           if ($component == 'membership_civireport') {
 956             if ($val = CRM_Utils_Array::value('civicrm_membership_membership_status_id', $row)) {
 957               $componentRows[$contactID][$component][$rowNum]['civicrm_membership_membership_status_id'] = CRM_Member_PseudoConstant::membershipStatus($val);
 958             }
 959             $entryFound = TRUE;
 960           }
 961
 962           // skip looking further in rows, if first row itself doesn't
 963           // have the column we need
 964           if (!$entryFound) {
 965             break;
 966           }
 967         }
 968       }
 969     }
 970   }
 971
 972 }