CRM/Report/Form/Contribute/History.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | CiviCRM version 4.4                                                |
   6  +--------------------------------------------------------------------+
   7  | Copyright CiviCRM LLC (c) 2004-2013                                |
   8  +--------------------------------------------------------------------+
   9  | This file is a part of CiviCRM.                                    |
  10  |                                                                    |
  11  | CiviCRM is free software; you can copy, modify, and distribute it  |
  12  | under the terms of the GNU Affero General Public License           |
  13  | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  14  |                                                                    |
  15  | CiviCRM is distributed in the hope that it will be useful, but     |
  16  | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  17  | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  18  | See the GNU Affero General Public License for more details.        |
  19  |                                                                    |
  20  | You should have received a copy of the GNU Affero General Public   |
  21  | License and the CiviCRM Licensing Exception along                  |
  22  | with this program; if not, contact CiviCRM LLC                     |
  23  | at info[AT]civicrm[DOT]org. If you have questions about the        |
  24  | GNU Affero General Public License or the licensing of CiviCRM,     |
  25  | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  26  +--------------------------------------------------------------------+
  27 */
  28
  29 /**
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2013
  33  * $Id$
  34  *
  35  */
  36 class CRM_Report_Form_Contribute_History extends CRM_Report_Form {
  37   // Primary Contacts count limitCONSTROW_COUNT_LIMIT = 10;
  38
  39   protected $_addressField = FALSE;
  40   protected $_emailField = FALSE;
  41   protected $_phoneField = FALSE;
  42   protected $_relationshipColumns = array();
  43
  44   protected $_customGroupExtends = array('Contribution');
  45
  46   protected $_referenceYear = array(
  47     'this_year' => '',
  48     'other_year' => '',
  49   );
  50   protected $_yearStatisticsFrom = '';
  51
  52   protected $_yearStatisticsTo = '';
  53   function __construct() {
  54     $yearsInPast = 4;
  55     $date        = CRM_Core_SelectValues::date('custom', NULL, $yearsInPast, 0);
  56     $count       = $date['maxYear'];
  57     $optionYear  = array('' => ts('-- select --'));
  58
  59     $this->_yearStatisticsFrom = $date['minYear'];
  60     $this->_yearStatisticsTo = $date['maxYear'];
  61
  62     while ($date['minYear'] <= $count) {
  63       $optionYear[$date['minYear']] = $date['minYear'];
  64       $date['minYear']++;
  65     }
  66
  67     $relationTypeOp = array();
  68     $relationshipTypes = CRM_Core_PseudoConstant::relationshipType();
  69     foreach ($relationshipTypes as $rid => $rtype) {
  70       if ($rtype['label_a_b'] != $rtype['label_b_a']) {
  71         $relationTypeOp[$rid] = "{$rtype['label_a_b']}/{$rtype['label_b_a']}";
  72       }
  73       else {
  74         $relationTypeOp[$rid] = $rtype['label_a_b'];
  75       }
  76     }
  77
  78     $this->_columns = array(
  79       'civicrm_contact' =>
  80       array(
  81         'dao' => 'CRM_Contact_DAO_Contact',
  82         'fields' =>
  83         array(
  84           'sort_name' =>
  85           array('title' => ts('Contact Name'),
  86             'default' => TRUE,
  87             'required' => TRUE,
  88             'no_repeat' => TRUE,
  89           ),
  90           'id' =>
  91           array(
  92             'no_display' => TRUE,
  93             'default' => TRUE,
  94             'required' => TRUE,
  95           ),
  96           'contact_type' =>
  97           array(
  98             'title' => ts('Contact Type'),
  99           ),
 100           'contact_sub_type' =>
 101           array(
 102             'title' => ts('Contact SubType'),
 103           ),
 104         ),
 105         'grouping' => 'contact-fields',
 106         'filters' =>
 107         array(
 108           'sort_name' =>
 109           array('title' => ts('Contact Name')),
 110           'id' =>
 111           array('title' => ts('Contact ID'),
 112             'no_display' => TRUE,
 113           ),
 114         ),
 115       ),
 116       'civicrm_email' =>
 117       array(
 118         'dao' => 'CRM_Core_DAO_Email',
 119         'fields' =>
 120         array(
 121           'email' =>
 122           array('title' => ts('Email'),
 123             'no_repeat' => TRUE,
 124           ),
 125         ),
 126         'grouping' => 'contact-fields',
 127       ),
 128       'civicrm_phone' =>
 129       array(
 130         'dao' => 'CRM_Core_DAO_Phone',
 131         'fields' =>
 132         array(
 133           'phone' =>
 134           array('title' => ts('Phone'),
 135             'no_repeat' => TRUE,
 136           ),
 137         ),
 138         'grouping' => 'contact-fields',
 139       ),
 140     ) + $this->addAddressFields(FALSE, FALSE, FALSE, array(
 141       )) + array('civicrm_relationship' =>
 142       array(
 143         'dao' => 'CRM_Contact_DAO_Relationship',
 144         'fields' =>
 145         array(
 146           'relationship_type_id' =>
 147           array('title' => ts('Relationship Type'),
 148             'default' => TRUE,
 149           ),
 150           'contact_id_a' =>
 151           array('no_display' => TRUE),
 152           'contact_id_b' =>
 153           array('no_display' => TRUE),
 154         ),
 155         'filters' =>
 156         array(
 157           'relationship_type_id' =>
 158           array('title' => ts('Relationship Type'),
 159             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 160             'options' => $relationTypeOp,
 161             'type' => CRM_Utils_Type::T_STRING,
 162           ),
 163         ),
 164       ),
 165     ) + array(
 166       'civicrm_contribution' =>
 167       array(
 168         'dao' => 'CRM_Contribute_DAO_Contribution',
 169         'fields' =>
 170         array(
 171           'total_amount' =>
 172           array('title' => ts('Amount Statistics'),
 173             'default' => TRUE,
 174             'required' => TRUE,
 175             'no_display' => TRUE,
 176             'statistics' =>
 177             array('sum' => ts('Aggregate Amount')),
 178           ),
 179           'receive_date' =>
 180           array(
 181             'required' => TRUE,
 182             'default' => TRUE,
 183             'no_display' => TRUE,
 184           ),
 185         ),
 186         'grouping' => 'contri-fields',
 187         'filters' =>
 188         array(
 189           'this_year' =>
 190           array(
 191             'title' => ts('This Year'),
 192             'operatorType' => CRM_Report_Form::OP_SELECT,
 193             'options' => $optionYear,
 194             'default' => '',
 195           ),
 196           'other_year' =>
 197           array(
 198             'title' => ts('Other Years'),
 199             'operatorType' => CRM_Report_Form::OP_SELECT,
 200             'options' => $optionYear,
 201             'default' => '',
 202           ),
 203           'receive_date' =>
 204           array('operatorType' => CRM_Report_Form::OP_DATE),
 205           'contribution_status_id' =>
 206           array('title' => ts('Donation Status'),
 207             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 208             'options' => CRM_Contribute_PseudoConstant::contributionStatus(),
 209             'default' => array(1),
 210           ),
 211           'financial_type_id' => array(
 212             'title' => ts('Financial Type'),
 213             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 214             'options' => CRM_Contribute_PseudoConstant::financialType(),
 215           ),
 216           'total_amount' =>
 217           array('title' => ts('Donation Amount'),
 218           ),
 219           'total_sum' =>
 220           array('title' => ts('Aggregate Amount'),
 221             'type' => CRM_Report_Form::OP_INT,
 222             'dbAlias' => 'civicrm_contribution_total_amount_sum',
 223             'having' => TRUE,
 224           ),
 225         ),
 226       ),
 227     ) + array(
 228       'civicrm_group' =>
 229       array(
 230         'dao' => 'CRM_Contact_DAO_GroupContact',
 231         'alias' => 'cgroup',
 232         'filters' =>
 233         array(
 234           'gid' =>
 235           array(
 236             'name' => 'group_id',
 237             'title' => ts('Group'),
 238             'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 239             'group' => TRUE,
 240             'options' => CRM_Core_PseudoConstant::group(),
 241           ),
 242         ),
 243       ),
 244     );
 245
 246     $this->_columns['civicrm_contribution']['fields']['civicrm_upto_' . $this->_yearStatisticsFrom] = array('title' => ts('Up To %1 Donation', array(1 => $this->_yearStatisticsFrom)),
 247       'default' => TRUE,
 248       'type' => CRM_Utils_Type::T_MONEY,
 249       'is_statistics' => TRUE,
 250     );
 251
 252     $yearConter = $this->_yearStatisticsFrom;
 253     $yearConter++;
 254     while ($yearConter <= $this->_yearStatisticsTo) {
 255       $this->_columns['civicrm_contribution']['fields'][$yearConter] = array('title' => ts('%1 Donation', array(1 => $yearConter)),
 256         'default' => TRUE,
 257         'type' => CRM_Utils_Type::T_MONEY,
 258         'is_statistics' => TRUE,
 259       );
 260       $yearConter++;
 261     }
 262
 263     $this->_columns['civicrm_contribution']['fields']['aggregate_amount'] = array('title' => ts('Aggregate Amount'),
 264       'type' => CRM_Utils_Type::T_MONEY,
 265       'is_statistics' => TRUE,
 266     );
 267
 268     $this->_tagFilter = TRUE;
 269     parent::__construct();
 270   }
 271
 272   function preProcess() {
 273     parent::preProcess();
 274   }
 275
 276   function select() {
 277     $select = array();
 278     $this->_columnHeaders = array();
 279
 280     foreach ($this->_columns as $tableName => $table) {
 281       if (array_key_exists('fields', $table)) {
 282         foreach ($table['fields'] as $fieldName => $field) {
 283
 284           if (!empty($field['required']) ||
 285             CRM_Utils_Array::value($fieldName, $this->_params['fields'])
 286           ) {
 287             if ($tableName == 'civicrm_address') {
 288               $this->_addressField = TRUE;
 289             }
 290             if ($tableName == 'civicrm_email') {
 291               $this->_emailField = TRUE;
 292             }
 293             if ($tableName == 'civicrm_phone') {
 294               $this->_phoneField = TRUE;
 295             }
 296             if ($tableName == 'civicrm_relationship') {
 297               $this->_relationshipColumns["{$tableName}_{$fieldName}"] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 298               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 299               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 300               continue;
 301             }
 302
 303             if (!empty($field['is_statistics'])) {
 304               $this->_columnHeaders[$fieldName]['type'] = $field['type'];
 305               $this->_columnHeaders[$fieldName]['title'] = $field['title'];
 306               continue;
 307             }
 308             elseif ($fieldName == 'receive_date') {
 309                 if ((CRM_Utils_Array::value('this_year_op', $this->_params) == 'fiscal' &&
 310                      CRM_Utils_Array::value('this_year_value', $this->_params)) ||
 311                     (CRM_Utils_Array::value('other_year_op', $this->_params == 'fiscal') &&
 312                       CRM_Utils_Array::value('other_year_value', $this->_params)
 313                      )){
 314                     $select[] = self::fiscalYearOffset($field['dbAlias']) . " as {$tableName}_{$fieldName}";
 315                 }else{
 316                     $select[] = " YEAR(".$field['dbAlias'].")" . " as {$tableName}_{$fieldName}";
 317                 }
 318             }
 319             elseif ($fieldName == 'total_amount') {
 320               $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
 321             }
 322             else {
 323               $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 324               $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
 325               $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
 326             }
 327             if (!empty($field['no_display'])) {
 328               $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = TRUE;
 329             }
 330           }
 331         }
 332       }
 333     }
 334
 335     $this->_select = "SELECT " . implode(', ', $select) . " ";
 336   }
 337
 338   function from() {
 339     $this->_from = "
 340         FROM civicrm_contact  {$this->_aliases['civicrm_contact']}
 341              INNER JOIN civicrm_contribution   {$this->_aliases['civicrm_contribution']}
 342                      ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_contribution']}.contact_id AND
 343                         {$this->_aliases['civicrm_contribution']}.is_test = 0 ";
 344
 345     if ($this->_emailField) {
 346       $this->_from .= " LEFT JOIN civicrm_email {$this->_aliases['civicrm_email']}
 347                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_email']}.contact_id AND
 348                         {$this->_aliases['civicrm_email']}.is_primary = 1) ";
 349     }
 350
 351     if ($this->_phoneField) {
 352       $this->_from .= " LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
 353                      ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
 354                         {$this->_aliases['civicrm_phone']}.is_primary = 1) ";
 355     }
 356
 357     $relContacAlias = 'contact_relationship';
 358     $this->_relationshipFrom = " INNER JOIN civicrm_relationship {$this->_aliases['civicrm_relationship']}
 359                      ON (({$this->_aliases['civicrm_relationship']}.contact_id_a = {$relContacAlias}.id OR {$this->_aliases['civicrm_relationship']}.contact_id_b = {$relContacAlias}.id ) AND {$this->_aliases['civicrm_relationship']}.is_active = 1) ";
 360
 361     if ($this->_addressField) {
 362       $this->_from .= "
 363                   LEFT JOIN civicrm_address {$this->_aliases['civicrm_address']}
 364                          ON {$this->_aliases['civicrm_contact']}.id =
 365                             {$this->_aliases['civicrm_address']}.contact_id AND
 366                             {$this->_aliases['civicrm_address']}.is_primary = 1\n";
 367     }
 368   }
 369
 370   function where() {
 371     $whereClauses = $havingClauses = $relationshipWhere = array();
 372     $this->_relationshipWhere = '';
 373     $this->_statusClause = '';
 374
 375     foreach ($this->_columns as $tableName => $table) {
 376       if (array_key_exists('filters', $table)) {
 377         foreach ($table['filters'] as $fieldName => $field) {
 378           $clause = NULL;
 379           if ($fieldName == 'this_year' || $fieldName == 'other_year') {
 380             continue;
 381           }elseif (CRM_Utils_Array::value('type', $field) & CRM_Utils_Type::T_DATE) {
 382             $relative = CRM_Utils_Array::value("{$fieldName}_relative", $this->_params);
 383             $from     = CRM_Utils_Array::value("{$fieldName}_from", $this->_params);
 384             $to       = CRM_Utils_Array::value("{$fieldName}_to", $this->_params);
 385
 386             $clause = $this->dateClause($field['name'], $relative, $from, $to, $field['type']);
 387           }
 388           else {
 389             $op = CRM_Utils_Array::value("{$fieldName}_op", $this->_params);
 390             if ($op) {
 391               $clause = $this->whereClause($field,
 392                 $op,
 393                 CRM_Utils_Array::value("{$fieldName}_value", $this->_params),
 394                 CRM_Utils_Array::value("{$fieldName}_min", $this->_params),
 395                 CRM_Utils_Array::value("{$fieldName}_max", $this->_params)
 396               );
 397             }
 398           }
 399
 400           if (!empty($clause)) {
 401             if ($tableName == 'civicrm_relationship') {
 402               $relationshipWhere[] = $clause;
 403               continue;
 404             }
 405
 406             if ($fieldName == 'contribution_status_id') {
 407               $this->_statusClause = " AND " . $clause;
 408             }
 409
 410             if (!empty($field['having'])) {
 411               $havingClauses[] = $clause;
 412             }
 413             else {
 414               $whereClauses[] = $clause;
 415             }
 416           }
 417         }
 418       }
 419     }
 420
 421     if (empty($whereClauses)) {
 422       $this->_where = "WHERE ( 1 ) ";
 423       $this->_having = "";
 424     }
 425     else {
 426       $this->_where = "WHERE " . implode(' AND ', $whereClauses);
 427     }
 428
 429     if ($this->_aclWhere) {
 430       $this->_where .= " AND {$this->_aclWhere} ";
 431     }
 432
 433     if (!empty($havingClauses)) {
 434       // use this clause to construct group by clause.
 435       $this->_having = "HAVING " . implode(' AND ', $havingClauses);
 436     }
 437
 438     if (!empty($relationshipWhere)) {
 439       $this->_relationshipWhere = ' AND ' . implode(' AND ', $relationshipWhere);
 440     }
 441   }
 442
 443   function groupBy() {
 444     $this->_groupBy = "GROUP BY {$this->_aliases['civicrm_contribution']}.contact_id, YEAR({$this->_aliases['civicrm_contribution']}.receive_date)";
 445   }
 446
 447   //Override to set limit is 10
 448   function limit($rowCount = self::ROW_COUNT_LIMIT) {
 449     parent::limit($rowCount);
 450   }
 451
 452   //Override to set pager with limit is 10
 453   function setPager($rowCount = self::ROW_COUNT_LIMIT) {
 454     parent::setPager($rowCount);
 455   }
 456
 457   function statistics(&$rows) {
 458     $statistics = parent::statistics($rows);
 459     $count = 0;
 460     foreach ($rows as $rownum => $row) {
 461       if (is_numeric($rownum)) {
 462         $count++;
 463       }
 464     }
 465     $statistics['counts']['rowCount'] = array('title' => ts('Primary Contact(s) Listed'),
 466       'value' => $count,
 467     );
 468
 469     if ($this->_rowsFound && ($this->_rowsFound > $count)) {
 470       $statistics['counts']['rowsFound'] = array('title' => ts('Total Primary Contact(s)'),
 471         'value' => $this->_rowsFound,
 472       );
 473     }
 474
 475     return $statistics;
 476   }
 477
 478   static function formRule($fields, $files, $self) {
 479     $errors = array();
 480     if (!empty($fields['this_year_value']) &&
 481       CRM_Utils_Array::value('other_year_value', $fields) &&
 482       ($fields['this_year_value'] == $fields['other_year_value'])
 483     ) {
 484       $errors['other_year_value'] = ts("Value for filters 'This Year' and 'Other Years' can not be same.");
 485     }
 486     return $errors;
 487   }
 488
 489   function postProcess() {
 490     // get ready with post process params
 491     $this->beginPostProcess();
 492     $this->fixReportParams();
 493
 494     $this->buildACLClause($this->_aliases['civicrm_contact']);
 495     $this->select();
 496     $this->where();
 497     $this->from();
 498     $this->groupBy();
 499
 500     $rows = array();
 501
 502     // build array of result based on column headers. This method also allows
 503     // modifying column headers before using it to build result set i.e $rows.
 504     $this->buildRows($rows);
 505
 506     // format result set.
 507     $this->formatDisplay($rows, FALSE);
 508
 509     // assign variables to templates
 510     $this->doTemplateAssignment($rows);
 511
 512     // do print / pdf / instance stuff if needed
 513     $this->endPostProcess($rows);
 514   }
 515
 516   function fixReportParams() {
 517     if (!empty($this->_params['this_year_value'])) {
 518       $this->_referenceYear['this_year'] = $this->_params['this_year_value'];
 519     }
 520     if (!empty($this->_params['other_year_value'])) {
 521       $this->_referenceYear['other_year'] = $this->_params['other_year_value'];
 522     }
 523   }
 524
 525   function buildRows(&$rows) {
 526     $contactIds = array();
 527
 528     $addWhere = '';
 529
 530     if (!empty($this->_referenceYear['other_year'])) {
 531         (CRM_Utils_Array::value('other_year_op', $this->_params) == 'calendar') ? $other_receive_date = 'YEAR (contri.receive_date)' : $other_receive_date = self::fiscalYearOffset('contri.receive_date');
 532         $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id NOT IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE  cont.id = contri.contact_id AND {$other_receive_date} = {$this->_referenceYear['other_year']} AND contri.is_test = 0 ) ";
 533     }
 534     if (!empty($this->_referenceYear['this_year'])) {
 535         (CRM_Utils_Array::value('this_year_op', $this->_params) == 'calendar') ? $receive_date = 'YEAR (contri.receive_date)' : $receive_date = self::fiscalYearOffset('contri.receive_date');
 536         $addWhere .= " AND {$this->_aliases['civicrm_contact']}.id IN ( SELECT DISTINCT cont.id FROM civicrm_contact cont, civicrm_contribution contri WHERE cont.id = contri.contact_id AND {$receive_date} = {$this->_referenceYear['this_year']} AND contri.is_test = 0 ) ";
 537     }
 538     $this->limit();
 539     $getContacts = "SELECT SQL_CALC_FOUND_ROWS {$this->_aliases['civicrm_contact']}.id as cid, SUM({$this->_aliases['civicrm_contribution']}.total_amount) as civicrm_contribution_total_amount_sum {$this->_from} {$this->_where} {$addWhere} GROUP BY {$this->_aliases['civicrm_contact']}.id {$this->_having} {$this->_limit}";
 540
 541     $dao = CRM_Core_DAO::executeQuery($getContacts);
 542
 543     while ($dao->fetch()) {
 544       $contactIds[] = $dao->cid;
 545     }
 546     $dao->free();
 547     $this->setPager();
 548
 549     $relationshipRows = array();
 550     if (empty($contactIds)) {
 551       return;
 552     }
 553
 554     $primaryContributions = $this->buildContributionRows($contactIds);
 555
 556     list($relationshipRows, $relatedContactIds) = $this->buildRelationshipRows($contactIds);
 557
 558     if (empty($relatedContactIds)) {
 559       $rows = $primaryContributions;
 560       return;
 561     }
 562
 563     $relatedContributions = $this->buildContributionRows($relatedContactIds);
 564
 565     $summaryYears   = array();
 566     $summaryYears[] = "civicrm_upto_{$this->_yearStatisticsFrom}";
 567     $yearConter     = $this->_yearStatisticsFrom;
 568     $yearConter++;
 569     while ($yearConter <= $this->_yearStatisticsTo) {
 570       $summaryYears[] = $yearConter;
 571       $yearConter++;
 572     }
 573     $summaryYears[] = 'aggregate_amount';
 574
 575     foreach ($primaryContributions as $cid => $primaryRow) {
 576       $row = $primaryRow;
 577       if (!isset($relationshipRows[$cid])) {
 578         $rows[$cid] = $row;
 579         continue;
 580       }
 581       $total = array();
 582       $total['civicrm_contact_sort_name'] = ts('Total');
 583       foreach ($summaryYears as $year) {
 584         $total[$year] = CRM_Utils_Array::value($year, $primaryRow, 0);
 585       }
 586
 587       $relatedContact = FALSE;
 588       $rows[$cid] = $row;
 589       foreach ($relationshipRows[$cid] as $relcid => $relRow) {
 590         if (!isset($relatedContributions[$relcid])) {
 591           continue;
 592         }
 593         $relatedContact = TRUE;
 594         $relatedRow = $relatedContributions[$relcid];
 595         foreach ($summaryYears as $year) {
 596           $total[$year] += CRM_Utils_Array::value($year, $relatedRow, 0);
 597         }
 598
 599         foreach (array_keys($this->_relationshipColumns) as $col) {
 600           if (!empty($relRow[$col])) {
 601             $relatedRow[$col] = $relRow[$col];
 602           }
 603         }
 604         $rows["{$cid}_{$relcid}"] = $relatedRow;
 605       }
 606       if ($relatedContact) {
 607         $rows["{$cid}_total"] = $total;
 608         $rows["{$cid}_bank"] = array('civicrm_contact_sort_name' => '&nbsp;');
 609       }
 610     }
 611   }
 612
 613   function buildContributionRows($contactIds) {
 614     $rows = array();
 615     if (empty($contactIds)) {
 616       return $rows;
 617     }
 618
 619     $sqlContribution = "{$this->_select} {$this->_from} WHERE {$this->_aliases['civicrm_contact']}.id IN (" . implode(',', $contactIds) . ") AND {$this->_aliases['civicrm_contribution']}.is_test = 0 {$this->_statusClause} {$this->_groupBy} ";
 620
 621     $dao             = CRM_Core_DAO::executeQuery($sqlContribution);
 622     $contributionSum = 0;
 623     $yearcal         = array();
 624     while ($dao->fetch()) {
 625       if (!$dao->civicrm_contact_id) {
 626         continue;
 627       }
 628
 629       foreach ($this->_columnHeaders as $key => $value) {
 630         if (property_exists($dao, $key)) {
 631           $rows[$dao->civicrm_contact_id][$key] = $dao->$key;
 632         }
 633       }
 634       if ($dao->civicrm_contribution_receive_date) {
 635         if ($dao->civicrm_contribution_receive_date > $this->_yearStatisticsFrom) {
 636           $rows[$dao->civicrm_contact_id][$dao->civicrm_contribution_receive_date] = $dao->civicrm_contribution_total_amount;
 637         }
 638         else {
 639           if (!isset($rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"])) {
 640             $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] = 0;
 641           }
 642
 643           $rows[$dao->civicrm_contact_id]["civicrm_upto_{$this->_yearStatisticsFrom}"] += $dao->civicrm_contribution_total_amount;
 644         }
 645       }
 646
 647       if (!isset($rows[$dao->civicrm_contact_id]['aggregate_amount'])) {
 648         $rows[$dao->civicrm_contact_id]['aggregate_amount'] = 0;
 649       }
 650       $rows[$dao->civicrm_contact_id]['aggregate_amount'] += $dao->civicrm_contribution_total_amount;
 651     }
 652     $dao->free();
 653     return $rows;
 654   }
 655
 656   function buildRelationshipRows($contactIds) {
 657     $relationshipRows = $relatedContactIds = array();
 658     if (empty($contactIds)) {
 659       return array($relationshipRows, $relatedContactIds);
 660     }
 661
 662     $relContactAlias = 'contact_relationship';
 663     $addRelSelect = '';
 664     if (!empty($this->_relationshipColumns)) {
 665       $addRelSelect = ', ' . implode(', ', $this->_relationshipColumns);
 666     }
 667     $sqlRelationship = "SELECT {$this->_aliases['civicrm_relationship']}.relationship_type_id as relationship_type_id, {$this->_aliases['civicrm_relationship']}.contact_id_a as contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b as contact_id_b {$addRelSelect} FROM civicrm_contact {$relContactAlias} {$this->_relationshipFrom} WHERE {$relContactAlias}.id IN (" . implode(',', $contactIds) . ") AND {$this->_aliases['civicrm_relationship']}.is_active = 1 {$this->_relationshipWhere} GROUP BY {$this->_aliases['civicrm_relationship']}.contact_id_a, {$this->_aliases['civicrm_relationship']}.contact_id_b";
 668     $relationshipTypes = CRM_Core_PseudoConstant::relationshipType();
 669
 670     $dao = CRM_Core_DAO::executeQuery($sqlRelationship);
 671     while ($dao->fetch()) {
 672       $row = array();
 673       foreach (array_keys($this->_relationshipColumns) as $rel_column) {
 674         $row[$rel_column] = $dao->$rel_column;
 675       }
 676       if (in_array($dao->contact_id_a, $contactIds)) {
 677         $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_a_b'];
 678         $row['civicrm_relationship_contact_id'] = $dao->contact_id_b;
 679         $relationshipRows[$dao->contact_id_a][$dao->contact_id_b] = $row;
 680         $relatedContactIds[$dao->contact_id_b] = $dao->contact_id_b;
 681       }
 682       if (in_array($dao->contact_id_b, $contactIds)) {
 683         $row['civicrm_relationship_contact_id'] = $dao->contact_id_a;
 684         $row['civicrm_relationship_relationship_type_id'] = $relationshipTypes[$dao->relationship_type_id]['label_b_a'];
 685         $relationshipRows[$dao->contact_id_b][$dao->contact_id_a] = $row;
 686         $relatedContactIds[$dao->contact_id_a] = $dao->contact_id_a;
 687       }
 688     }
 689     $dao->free();
 690     return array($relationshipRows, $relatedContactIds);
 691   }
 692
 693   // Override "This Year" $op options
 694   function getOperationPair($type = "string", $fieldName = NULL) {
 695     if ($fieldName == 'this_year' || $fieldName == 'other_year') {
 696       return array('calendar' => ts('Is Calendar Year'), 'fiscal' => ts('Fiscal Year Starting'));
 697     }
 698     return parent::getOperationPair($type, $fieldName);
 699   }
 700
 701   function alterDisplay(&$rows) {
 702     if (empty($rows)) {
 703       return;
 704     }
 705
 706
 707     $last_primary = NULL;
 708     foreach ($rows as $rowNum => $row) {
 709       // Highlight primary contact and amount row
 710       if (is_numeric($rowNum) ||
 711         ($last_primary && ($rowNum == "{$last_primary}_total"))
 712       ) {
 713         if (is_numeric($rowNum)) {
 714           $last_primary = $rowNum;
 715         }
 716         foreach ($row as $key => $value) {
 717           if ($key == 'civicrm_contact_id') {
 718             continue;
 719           }
 720           if (empty($value)) {
 721             $row[$key] = '';
 722             continue;
 723           }
 724
 725           if ($last_primary && ($rowNum == "{$last_primary}_total")) {
 726             $value = CRM_Utils_Money::format($value, ' ');
 727           }
 728           $row[$key] = '<strong>' . $value . '</strong>';
 729         }
 730         $rows[$rowNum] = $row;
 731       }
 732
 733       // Convert Display name into link
 734       if (!empty($row['civicrm_contact_sort_name']) &&
 735         CRM_Utils_Array::value('civicrm_contact_id', $row)
 736       ) {
 737         $url = CRM_Report_Utils_Report::getNextUrl('contribute/detail',
 738           'reset=1&force=1&id_op=eq&id_value=' . $row['civicrm_contact_id'],
 739           $this->_absoluteUrl, $this->_id
 740         );
 741         $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
 742         $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contribution Details for this Contact.");
 743       }
 744     }
 745   }
 746  }
 747