CRM/Report/Form/Contact/Detail.php

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