crm.fsf.org/20131203/files/sites/all/modules-old/civicrm/CRM/Report/Form/Contact/Detail.php

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