CRM/Report/Form/Contribute/History.php

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