CRM/Report/Form/Contribute/History.php

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